# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

from ast import If
from webbrowser import get
import frappe
from frappe.core.doctype.user.user import User
from frappe.model.document import Document
from frappe.permissions import add_user_permission
import requests
import json
from frappe import _, format_value, get_doc, msgprint
from frappe.utils import now, add_to_date, get_date_str, today, validate_email_address
import datetime
from srm.srm.erp_sync import K3CloudSync
from srm.srm.erp_sync_stock import K3CloudSync as K3CloudSync_stock

# frappe.utils.logger.set_log_level("DEBUG")
logger = frappe.logger("api", allow_site=True, file_count=50)


class SRMServerConfig(Document):
    def validate(self):
        if self.disable == 0:
            num = frappe.db.count('SRM Server Config', {'disable': 0})
            if num > 1:
                frappe.throw('为了数据准确,只允许有一个在启用的服务器配置!请禁用之前的配置!')


# 2023-05-22反写云星空的采购订单行字段: 订单答交明细
@frappe.whitelist()
def write_erp_po_entryfield(erp_fid, poentryids, f_xcd_combo1=1):
    acctid = frappe.get_all('SRM Demo AcctID', filters={'enable': 1, 'application': '采购订单'},fields=['acctid'])
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.get_all('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])
    
    if acctid:
        # 取测试账套ID  63b7c666bf6b3c
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    # 调用登录函数
    if login_response:
        # 构建保存接口URL
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        # 构建Json字符串(预先在云星空的WEB API中先调试生效)
        # 是否删除已存在的分录:IsDeleteEntry
        post_options = {
            "IsDeleteEntry": "false",
            "Model": {
                "FID": erp_fid,
                "FPOOrderEntry": poentryids,
                "f_xcd_combo1": f_xcd_combo1,
            }
        }
        # 序列化json.dumps()
        post_data = { "FormID": "PUR_PurchaseOrder", "Data": json.dumps(post_options) }
        # 同步至K3cloud
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        r = json.loads(save_response.text)
        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
            return r["Result"]["Number"]
        else:
            return r["Result"]["ResponseStatus"]["Errors"][0]["Message"]


# [供应商导入审批单]定时任务
@frappe.whitelist()
def schedule_sync_to_oa_workfolw():
    # 定期把新的审批单推送至OA
    l = frappe.get_all('SRM Supplier Approval L2L3', filters={'workflow_state': "总监审核", 'oa_billno': None, 'modified': ['>=', today()]})
    if len(l) > 0:
        for i in l:
            doc = frappe.get_doc('SRM Supplier Approval L2L3', i.name)
            doc.sync_to_oa_workfolw()
    # 定期整点从OA更新过来的审批单(同意)添加approve_by 
    # 2023-11-13优化: 已经在python脚本中实现, 注释掉
    # l2 = frappe.get_all('SRM Supplier Approval L2L3', filters={'workflow_state': "Finish", 'oa_billno': ['!=', None], 'approve_by': None, 'modified': ['>=', today()]})
    # if len(l2) > 0:
    #     for i in l2:
    #         frappe.db.set_value('SRM Supplier Approval L2L3', i.name, {'approve_by': '林榕'})
    # 定期整点从OA更新过来的审批单(驳回)添加reject_by 
    # 2023-11-13优化: 已经在python脚本中实现, 注释掉
    # l3 = frappe.get_all('SRM Supplier Approval L2L3', filters={'workflow_state': "Rejected", 'oa_billno': ['!=', None], 'reject_by': None, 'modified': ['>=', today()]})
    # if len(l3) > 0:
    #     for i in l3:
    #         frappe.db.set_value('SRM Supplier Approval L2L3', i.name, {'reject_by': '林榕'})


# 写入K3Cloud[供应商协同采购回复(九联)] 2023-02-19: 由py中调用;传参为list
@frappe.whitelist()
def sync_to_k3cloud_plan_of_answer(entry_list, orgid = 'UM01', group_nubmer='01'):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '交期回复表'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID  63b7c666bf6b3c
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    # 调用登录函数
    if login_response:
        # 构建保存接口URL
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        # 构建k3Cloud的传参参数，其中{}是从API中复制得来
        fentity = []
        # frappe.msgprint(entry_array)
        # entry_array = json.loads(entry_array)
        for i in entry_list:
            if i[0] and i[1] and i[2]:
                fentity.append({
                            "FEntryID": 0,
                            "FPOENTRYID": i[2],
                            "FPOBILLNO": "",
                            "FMATERIALLID": {
                                "FNUMBER": ""
                            },
                            "FEXINSTOCKDATE": i[0],
                            "FQty": i[1]
                        })
        if len(fentity) > 0:
            post_options = {           
                "Model": { "FEntity": fentity }
            }
        # 序列化json.dumps()
        post_data = { "FormID": "JLKJ_GYSXTCGHF", "Data": json.dumps(post_options) }
        # 同步至K3cloud
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        r = json.loads(save_response.text)
        return r

# 2023-11-13: 从采购员录入的送货单-写入K3Cloud[供应商协同采购回复(九联)] 
@frappe.whitelist()
def sync_to_k3cloud_plan_of_answer_from_delivery(entry_list, orgid = 'UM01', group_nubmer='01'):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '交期回复表'},fields=['acctid'], ignore_permissions=True)
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if login_response:
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        fentity = []
        for i in entry_list:
            if i[0] and i[1] and i[2]:
                fentity.append({
                            "FEntryID": 0,
                            "FPOENTRYID": i[2],
                            "FPOBILLNO": "",
                            "FMATERIALLID": {
                                "FNUMBER": ""
                            },
                            "FEXINSTOCKDATE": i[0],
                            "FQty": i[1]
                        })
        if len(fentity) > 0:
            post_options = {      
                # 2023-11-13: 相比于sync_to_k3cloud_plan_of_answer, 就是参数中多了FEXT  
                "Model": { "FEXT": "true", "FEntity": fentity }
            }
        post_data = { "FormID": "JLKJ_GYSXTCGHF", "Data": json.dumps(post_options) }
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        r = json.loads(save_response.text)
        return r


# 从ERP采购交期回复表-采购员 同步生成采购交期回复表-供应商 2023-03-29优化:先组合了dict再insert
@frappe.whitelist()
def sync_plan_of_supplier_from_erp():
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '交期回复表'},fields=['acctid'], ignore_permissions=True)
    
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    if acctid:
        # 取测试账套ID  63b7c666bf6b3c
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps({
                # 2023-04-11: 从ERP同步主版本需求时只同步2周(当天日期+14Day)内的分配数量
                    "FormId": "JLKJ_CHGFJHCGY",
                    # F_JLKJ_SubEntity_FDetailID采购需求分配的实体主键, 纠正之前的F_JLKJ_Entity_FEntryID 
                    "FieldKeys": "FID, F_JLKJ_Entity_FEntryID, FBillNo, FPurchaserId.FName, F_JLKJ_CreatorId.FName, FISNEW, F_JLKJ_CreateDate, FPOENTRYID, FSUPPLIERID.FNumber, FSUPPLIERID.FName, FPOBILLNO, FMATERIALLID.FNumber, F_JLKJ_BaseProperty1, FMATERIALNAME, FMATERIALSPEC, FUnitID.FName, FPOQty, FINQty, FInspectQty, FText, Fsatisfy, FBillneedDate, FAssignQty, FPurchaserId.FName, FLeadTime, F_JLKJ_BaseProperty, F_JLKJ_SubEntity_FDetailID",
                    "FilterString": f" FISNEW = 1 AND FAssignQty >0 AND FBillneedDate > '2023-01-01' AND FBillneedDate <= '{add_to_date(today(), days = 14)}' ", # 只同步最新的,
                    "Limit": 0,
                    "OrderString": "FID, FSUPPLIERID.FNumber, FMATERIALLID.FNumber, FBillneedDate"      # 同一供应商按照物料编码\需求日期排序
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        # 判断res是否有效
        if res:
            # 判断当前最新版本的版本号是否已经存在,如果存在则不做任何处理
            if frappe.db.exists("SRM Supply Plan Of Supplier", {"fbillno": res[0][2]}):
                pass
            # 当前最新版本的版本号没有记录,即真正的最新版本需要同步
            else:
                # 定义plans初始化为空
                plans = []
                # 遍历res: 目的是根据采购员与供应商两个维度组合出来dict, 并append到plans
                for i in res:
                    # res的第一个元素,通过append插入plans
                    if len(plans) == 0:
                        plans.append({'doctype': 'SRM Supply Plan Of Supplier', 'fid': i[0], 'fbillno': i[2], 'buyer': i[3], 'creatorer': i[4], 'is_last': i[5], 'createdate': i[6], 'supplier_number': i[8], 'supplier_name': i[9].strip(), 'orderitem': [{'fentryid': i[1],'po_entryid': i[7],'po_billno': i[10],'material_number': i[11], 'old_material_number': i[12],'material_name': i[13].strip(),'material_model': i[14].strip(),'uom_name': i[15],'order_qty': i[16],'stock_qty': i[17],'inspect_qty': i[18],'description': i[19].strip(),'fsatisfy': i[20],'need_date': i[21],'assign_qty': i[22],'purchase_person': i[23],'lt': i[24],'mpq': i[25],'subentity_fdetailid': i[26] }]})
                    # 非res的第一个元素,必须匹配当前元素(i)的"fid"和"supplier_number"是否在plans最后一个元素存在
                    else:
                        # plans最后一个元素下标
                        index = len(plans) - 1
                        # 当前元素(i)的"fid"和"supplier_number"在plans最后一个元素存在,即"同一个采购员负责的同一个供应商"
                        if i[0] == plans[index]["fid"] and i[8] == plans[index]["supplier_number"]:
                            # plans[index]["orderitem"]插入新记录行,即表体行记录(同一个采购员负责的同一个供应商)
                            plans[index]["orderitem"].append({ 'fentryid': i[1],'po_entryid': i[7],'po_billno': i[10],'material_number': i[11],'old_material_number': i[12],'material_name': i[13].strip(),'material_model': i[14].strip(),'uom_name': i[15],'order_qty': i[16],'stock_qty': i[17],'inspect_qty': i[18],'description': i[19].strip(),'fsatisfy': i[20],'need_date': i[21],'assign_qty': i[22],'purchase_person': i[23],'lt': i[24],'mpq': i[25],'subentity_fdetailid': i[26] })
                        # 当前元素(i)的"fid"和"supplier_number"在plans最后一个元素存在,即"是一个新的采购员或者采购员的另一个供应商"
                        else:
                            # 把res的当前元素(i)通过append插入plans
                            plans.append({'doctype': 'SRM Supply Plan Of Supplier', 'fid': i[0], 'fbillno': i[2], 'buyer': i[3], 'creatorer': i[4], 'is_last': i[5], 'createdate': i[6], 'supplier_number': i[8], 'supplier_name': i[9].strip(), 'orderitem': [{'fentryid': i[1],'po_entryid': i[7],'po_billno': i[10],'material_number': i[11], 'old_material_number': i[12],'material_name': i[13].strip(),'material_model': i[14].strip(),'uom_name': i[15],'order_qty': i[16],'stock_qty': i[17],'inspect_qty': i[18],'description': i[19].strip(),'fsatisfy': i[20],'need_date': i[21],'assign_qty': i[22],'purchase_person': i[23],'lt': i[24],'mpq': i[25],'subentity_fdetailid': i[26] }]})
                # 通过遍历res得到plans, 此时才真正写入SRM Supply Plan Of Supplier
                if len(plans) > 0:
                    for plan in plans:
                        # 判断当前供应商档案是否存在,如果存在则开始创建
                        if frappe.db.exists("SRM Supplier", plan["supplier_number"]):
                            doc = frappe.get_doc(plan)
                            doc.insert(ignore_permissions=True)
                        elif frappe.db.exists("SRM Supplier", plan["supplier_number"]):
                                error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp', 'error': f'ERP的供应商编码{plan["supplier_number"]}与Name不一致'})
                                error.insert(ignore_permissions=True)
                        else:
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp', 'error': f'ERP的供应商编码{plan["supplier_number"]}不存在'})
                            error.insert(ignore_permissions=True)
        # 如果没有res返回
        else:
            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp', 'error': 'ERP没有符合最新的数据返回'})
            error.insert(ignore_permissions=True)


# 从ERP采购交期回复表-采购员(手工补录) 同步生成采购交期回复表-供应商: 补录的数据保存后直接关闭答交,只用来生成送货单
# 预计到货日期：FEXINSTOCKDate1 预计到货数量：FinstockQty 替换了原来的需求日期和分配数量
@frappe.whitelist()
def sync_plan_of_supplier_from_erp_add():
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    # 正式环境
    # login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    # 测试账套
    # acctid = "63b7c666bf6b3c"
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps({
                # 预计到货日期：FEXINSTOCKDate1 预计到货数量：FinstockQty 替换了原来的需求日期和分配数量
                    "FormId": "JLKJ_CHGFJHCGY",
                    "FieldKeys": "FID, F_JLKJ_Entity_FENTRYID, FBillNo, FPurchaserId.FName, F_JLKJ_CreatorId.FName, FISNEW, F_JLKJ_CreateDate, FPOENTRYID, FSUPPLIERID.FNumber, FSUPPLIERID.FName, FPOBILLNO, FMATERIALLID.FNumber, F_JLKJ_BaseProperty1, FMATERIALNAME, FMATERIALSPEC, FUnitID.FName, FPOQty, FINQty, FInspectQty, FText, Fsatisfy, FEXINSTOCKDate1, FinstockQty, FPurchaserId.FName, FLeadTime, F_JLKJ_BaseProperty, F_JLKJ_SubEntity1_FDetailID",
                    "FilterString": f" FISNEW = 1 AND F_JLKJ_XTXQ = 1 AND FEXINSTOCKDate1 <= '{add_to_date(today(), days = 14)}'" , # 只同步最新的并且F_JLKJ_XTXQ=1的,
                    "Limit": 0,
                    "OrderString": "FID, FSUPPLIERID.FNumber"
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        # 判断res是否有效
        if res:
            for i in range(len(res)):
                # FID存在判断分录行已存在
                if frappe.db.exists("SRM Supply Plan Of Supplier OrderItem",{"add_detail_id": res[i][26]} , cache=True):
                    pass
                # res中的fentryid不存在SRM Supply Plan Of Supplier OrderItem
                else:
                    # 舍去供货数量为0的记录行
                    if res[i][22] <= 0:
                        pass
                    else:
                        # 增加行记录之前判断供应商编码是否在SRM中存在并且一致,不一致的就跳过
                        if frappe.db.exists("SRM Supplier", res[i][8]):
                            doc = frappe.get_doc({
                                'doctype': 'SRM Supply Plan Of Supplier', 
                                'fid': f'{res[i][0]}',
                                'fbillno': res[i][2],
                                "is_answer": 1,                         # 补录的数据保存后直接关闭答交,只用来生成送货单
                                'buyer': res[i][3],
                                'creatorer': res[i][4],
                                'is_last': res[i][5],
                                'createdate': res[i][6],
                                'supplier_number': res[i][8],           # 供应商编码
                                'supplier_name': res[i][9].strip(),             # 供应商名称
                                'orderitem': [{
                                    'fentryid': res[i][1],                  # 行内码
                                    'po_entryid': res[i][7],                # 采购订单行内码
                                    'po_billno': res[i][10],                 # 采购订单号
                                    'material_number': res[i][11],           # 物料编码
                                    'old_material_number': res[i][12],       # 旧物料编码
                                    'material_name': res[i][13].strip(),             # 物料名称
                                    'material_model': res[i][14].strip(),            # 规格型号
                                    'uom_name': res[i][15],
                                    'order_qty': res[i][16],
                                    'stock_qty': res[i][17],
                                    'inspect_qty': res[i][18],
                                    'description': res[i][19].strip(),              #备注
                                    'fsatisfy': res[i][20],                 #满足需求
                                    'need_date': res[i][21],                #需求日期
                                    'assign_qty': res[i][22],               #分配数量
                                    'purchase_person': res[i][23],          #采购员
                                    'lt': res[i][24],
                                    'mpq': res[i][25],
                                    'answer_qty': res[i][22], 
                                    'answer_info': res[i][21] + "/" + str(res[i][22]),
                                    'add_detail_id': res[i][26]
                                }]
                            })
                            doc.insert(ignore_permissions=True)
                        elif frappe.db.exists("SRM Supplier", {"supplier_number": res[i][8]}):
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp_add', 'error': f'ERP的供应商编码{res[i][8]}与Name不一致'})
                            error.insert(ignore_permissions=True)
                        else:
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp_add', 'error': f'ERP的供应商编码{res[i][8]}不存在'})
                            error.insert(ignore_permissions=True)
                frappe.db.commit()
        # else:
        #     error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp_add', 'error': 'ERP没有符合最新的数据返回'})
        #     error.insert(ignore_permissions=True)


# 从ERP同步物料--冠运20220921
@frappe.whitelist()
def sync_material_from_erp():
    doc_name = frappe.db.get_list(
        "ERP Sync Config",
        filters={'fname': '物料主数据', 'enable': 1},
        fields=['name'], ignore_permissions=True
    )
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()

# 从ERP同步采购价目表--20230624
@frappe.whitelist()
def sync_pricecategory_from_erp():
    doc_name = frappe.db.get_all("ERP Sync Config",filters={'fname': '采购价目表', 'enable': 1})
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 从ERP同步采购订单--20221026
@frappe.whitelist()
def sync_po_from_erp():
    doc_name = frappe.db.get_list(
        "ERP Sync Config",
        filters={'fname': '采购订单', 'enable': 1},
        fields=['name'], ignore_permissions=True
    )
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 从ERP同步货源清单--20230424
@frappe.whitelist()
def sync_sourcebill_from_erp():
    doc_name = frappe.db.get_list(
        "ERP Sync Config",
        filters={'fname': '货源清单', 'enable': 1},
        fields=['name'], ignore_permissions=True
    )
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 2023-04-18:把未关闭的采购订单到ERP同步一次,保持单据的关闭状态、行业务关闭状态一致,用查看接口
@frappe.whitelist()
def new_day_sync_po_from_erp0():
    #2023-04-23: 'doc_closed' == 'A'是未关闭的采购订单
    po = frappe.db.get_all('SRM Purchase Order', filters={'doc_closed': 'A', 'date': ['<', frappe.utils.add_to_date(frappe.utils.today(), days=-2)], 'date': ['>=', '2023-03-01']}, fields=['name', 'erp_fid'] )
    server_config = frappe.db.get_all('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    view_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.View.common.kdsvc"
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if len(po) > 0 and login_response:
        for i in po:
            # 2023-07-28: 分化执行, 与3求余来判断
            if int(i.erp_fid)%3 == 0:
                doc = frappe.get_doc('SRM Purchase Order', i.name)
                view_options = {"FormId": "PUR_PurchaseOrder", "data": json.dumps({"Number": i.name})}
                view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
                view_res = json.loads(view_response.text)
                if view_res:
                    if view_res["Result"]["ResponseStatus"]["IsSuccess"] == True:
                        for item in view_res["Result"]["Result"]["POOrderEntry"]:
                            row = frappe.db.get_all('SRM Purchase Order Sub', filters = { 'po_entryid': item["Id"] })
                            if row:
                                frappe.db.set_value('SRM Purchase Order Sub', row[0].name, 
                                                        {   'closed': item["MRPCloseStatus"], 
                                                            'qty': item["Qty"], 
                                                            'request_date': item["DeliveryDate"], 
                                                            'price': item["Price"], 
                                                            'amount': item["Amount"], 
                                                            'tax_price': item["TaxPrice"], 
                                                            'tax_rate': item["TaxRate"], 
                                                            'all_amount': item["AllAmount"], 
                                                            'tax_amount': item["TaxAmount"], 
                                                            'freezestatus': item["MRPFreezeStatus"], 
                                                            'terminatestatus': item["MRPTerminateStatus"], 
                                                            'freceiveqty': item["ReceiveQty"], 
                                                            'fstockinqty': item["StockInQty"], 
                                                            'fremainreceiveqty': item["RemainReceiveQty"], 
                                                            'fremainstockinqty': item["RemainStockInQty"]
                                                        }, 
                                                        update_modified=True
                                                    )
                        # 2023-04-19: 更新单据头字段, 来源于view_res["Result"]["Result"]["CloseStatus"]
                        frappe.db.set_value(doc.doctype, doc.name, {'doc_closed': view_res["Result"]["Result"]["CloseStatus"], 'fclosestatus': '已关闭' if view_res["Result"]["Result"]["CloseStatus"] == 'B' else '未关闭'}, update_modified=True)
            frappe.db.commit()

@frappe.whitelist()
def new_day_sync_po_from_erp1():
    #2023-04-23: 'doc_closed' == 'A'是未关闭的采购订单
    po = frappe.db.get_all('SRM Purchase Order', filters={'doc_closed': 'A', 'date': ['<', frappe.utils.add_to_date(frappe.utils.today(), days=-2)], 'date': ['>=', '2023-03-01']}, fields=['name', 'erp_fid'] )
    server_config = frappe.db.get_all('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    view_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.View.common.kdsvc"
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if len(po) > 0 and login_response:
        for i in po:
            # 2023-07-28: 分化执行, 与3求余来判断
            if int(i.erp_fid)%3 == 1:
                doc = frappe.get_doc('SRM Purchase Order', i.name)
                view_options = {"FormId": "PUR_PurchaseOrder", "data": json.dumps({"Number": i.name})}
                view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
                view_res = json.loads(view_response.text)
                if view_res:
                    if view_res["Result"]["ResponseStatus"]["IsSuccess"] == True:
                        for item in view_res["Result"]["Result"]["POOrderEntry"]:
                            row = frappe.db.get_all('SRM Purchase Order Sub', filters = { 'po_entryid': item["Id"] })
                            if row:
                                frappe.db.set_value('SRM Purchase Order Sub', row[0].name, 
                                                        {   'closed': item["MRPCloseStatus"], 
                                                            'qty': item["Qty"], 
                                                            'request_date': item["DeliveryDate"], 
                                                            'price': item["Price"], 
                                                            'amount': item["Amount"], 
                                                            'tax_price': item["TaxPrice"], 
                                                            'tax_rate': item["TaxRate"], 
                                                            'all_amount': item["AllAmount"], 
                                                            'tax_amount': item["TaxAmount"], 
                                                            'freezestatus': item["MRPFreezeStatus"], 
                                                            'terminatestatus': item["MRPTerminateStatus"], 
                                                            'freceiveqty': item["ReceiveQty"], 
                                                            'fstockinqty': item["StockInQty"], 
                                                            'fremainreceiveqty': item["RemainReceiveQty"], 
                                                            'fremainstockinqty': item["RemainStockInQty"]
                                                        }, 
                                                        update_modified=True
                                                    )
                        # 2023-04-19: 更新单据头字段, 来源于view_res["Result"]["Result"]["CloseStatus"]
                        frappe.db.set_value(doc.doctype, doc.name, {'doc_closed': view_res["Result"]["Result"]["CloseStatus"], 'fclosestatus': '已关闭' if view_res["Result"]["Result"]["CloseStatus"] == 'B' else '未关闭'}, update_modified=True)
            frappe.db.commit()

@frappe.whitelist()
def new_day_sync_po_from_erp2():
    #2023-04-23: 'doc_closed' == 'A'是未关闭的采购订单
    po = frappe.db.get_all('SRM Purchase Order', filters={'doc_closed': 'A', 'date': ['<', frappe.utils.add_to_date(frappe.utils.today(), days=-2)], 'date': ['>=', '2023-03-01']}, fields=['name', 'erp_fid'] )
    server_config = frappe.db.get_all('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    view_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.View.common.kdsvc"
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if len(po) > 0 and login_response:
        for i in po:
            # 2023-07-28: 分化执行, 与3求余来判断
            if int(i.erp_fid)%3 == 2:
                doc = frappe.get_doc('SRM Purchase Order', i.name)
                view_options = {"FormId": "PUR_PurchaseOrder", "data": json.dumps({"Number": i.name})}
                view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
                view_res = json.loads(view_response.text)
                if view_res:
                    if view_res["Result"]["ResponseStatus"]["IsSuccess"] == True:
                        for item in view_res["Result"]["Result"]["POOrderEntry"]:
                            row = frappe.db.get_all('SRM Purchase Order Sub', filters = { 'po_entryid': item["Id"] })
                            if row:
                                frappe.db.set_value('SRM Purchase Order Sub', row[0].name, 
                                                        {   'closed': item["MRPCloseStatus"], 
                                                            'qty': item["Qty"], 
                                                            'request_date': item["DeliveryDate"], 
                                                            'price': item["Price"], 
                                                            'amount': item["Amount"], 
                                                            'tax_price': item["TaxPrice"], 
                                                            'tax_rate': item["TaxRate"], 
                                                            'all_amount': item["AllAmount"], 
                                                            'tax_amount': item["TaxAmount"], 
                                                            'freezestatus': item["MRPFreezeStatus"], 
                                                            'terminatestatus': item["MRPTerminateStatus"], 
                                                            'freceiveqty': item["ReceiveQty"], 
                                                            'fstockinqty': item["StockInQty"], 
                                                            'fremainreceiveqty': item["RemainReceiveQty"], 
                                                            'fremainstockinqty': item["RemainStockInQty"]
                                                        }, 
                                                        update_modified=True
                                                    )
                        # 2023-04-19: 更新单据头字段, 来源于view_res["Result"]["Result"]["CloseStatus"]
                        frappe.db.set_value(doc.doctype, doc.name, {'doc_closed': view_res["Result"]["Result"]["CloseStatus"], 'fclosestatus': '已关闭' if view_res["Result"]["Result"]["CloseStatus"] == 'B' else '未关闭'}, update_modified=True)
            frappe.db.commit()


# 指定PO到ERP查询PO的单据状态及行业务状态
@frappe.whitelist()
def sync_po_onebyone(po_billno):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps({
                        "FormId": "PUR_PurchaseOrder",
                        # PO号码-0,关闭状态-1,FEntryI-2,行业务关闭-3,采购数量-4,交货日期-5,采购单价-6,采购金额-7,含税单价-8,税率-9,含税金额-10,税额-11
                        # 2023-03-31增加一个字段: 制单人-12
                        # 2023-04-04增加: 币别-13, 审核人-14
                        # 2023-04-12增加: 业务冻结-15, 业务终止-16, 累计收料数量-17, 累计入库数量-18, 剩余收料数量-19, 剩余入库数量-20, 物料分类-21, 最小包装量-22
                        "FieldKeys": "FBillNo, FCloseStatus, FPOOrderEntry_FEntryID, FMRPCloseStatus, FQty, FDeliveryDate, FPrice, FEntryAmount, FTaxPrice, FEntryTaxRate, FAllAmount, FEntryTaxAmount, FCreatorId.FName, FSettleCurrId.FName, FApproverId.FName, FMRPFreezeStatus, FMRPTerminateStatus, FReceiveQty, FStockInQty, FRemainReceiveQty, FRemainStockINQty, F_JLKJ_WLFL, F_JLKJ_ZXBZL",
                        "FilterString": f" FDocumentStatus = 'C' AND FBillNo = '{po_billno}'"
                    })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        if res:
            return res


# 每天一次:把物料的规格型号与ERP同步一次
@frappe.whitelist()
def day_sync_material_from_erp():
    # 过滤三个月内创建的物料
    m = frappe.db.get_list('SRM Material', filters={'creation': ['>', add_to_date(today(), days=-150)]})
    # 2023-04-18: 经过实际验证, 如果全部物料1.7万条记录跑一次, 云星空会关闭连接(服务器过载), 必须取消
    # m = frappe.db.get_list('SRM Material')
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    view_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.View.common.kdsvc"
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if len(m) > 0 and login_response:
        for i in m:
            view_options = {"FormId": "BD_MATERIAL", "data": json.dumps({"Number": i.name})}
            view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
            view_res = json.loads(view_response.text)
            if view_res:
                if view_res["Result"]["ResponseStatus"]["IsSuccess"] == True:
                    specification = view_res["Result"]["Result"]["Specification"][0]["Value"]
                    doc = frappe.get_doc('SRM Material', i.name)
                    if specification.strip() != doc.material_model:
                        frappe.db.set_value(doc.doctype, doc.name, {"material_model": specification.strip()})
    frappe.db.commit()


# 从ERP同步采购订单变更单--20221028
@frappe.whitelist()
def sync_po_change_from_erp():
    doc_name = frappe.db.get_list(
        "ERP Sync Config",
        filters={'fname': '采购订单变更单', 'enable': 1},
        fields=['name']
    )
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 从ERP同步采购收料通知单--20221029
@frappe.whitelist()
def sync_receive_from_erp():
    doc_name = frappe.db.get_list(
        "ERP Sync Config",
        filters={'fname': '采购收料单', 'enable': 1},
        fields=['name'], ignore_permissions=True
    )
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 从ERP同步采购退料通知单[云星空的退料申请单]--20221029
@frappe.whitelist()
def sync_receive_return_from_erp():
    doc_name = frappe.db.get_list(
        "ERP Sync Config",
        filters={'fname': '采购退料单', 'enable': 1},
        fields=['name'], ignore_permissions=True
    )
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 从ERP同步采购入库单--20221029
@frappe.whitelist()
def sync_instock_from_erp():
    doc_name = frappe.db.get_all("ERP Sync Config", filters={'fname': '采购入库单', 'enable': 1}, fields=['name'])
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync_stock(doc_name[0].name)
        sync.sync()


# 从ERP同步采购退库单[云星空的采购退料单]--20221029
@frappe.whitelist()
def sync_instock_return_from_erp():
    doc_name = frappe.db.get_all("ERP Sync Config", filters={'fname': '采购退库单', 'enable': 1}, fields=['name'])
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 从ERP同步供应商:临时操作,因为SRM未正式上线--20221029
@frappe.whitelist()
def sync_supplier_from_erp():
    doc_name = frappe.db.get_list(
        "ERP Sync Config",
        filters={'fname': '供应商主数据', 'enable': 1},
        fields=['name'], ignore_permissions=True
    )
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 登录验证:校验服务器配置是否有效
@frappe.whitelist()
def login_validate(server_url, acctid, user_name, user_password): 
    # login_url 是登录函数的URL,
    login_url = server_url + "Kingdee.BOS.WebApi.ServicesStub.AuthService.ValidateUser.common.kdsvc"
	# 构建登录接口的传参
    login_data={"acctid": acctid, "username": user_name, "password":user_password ,"lcid": 2052}
    # 验证URL及参数
    # frappe.msgprint(f'the login_data is {login_data}')
    login_response = requests.post(url=login_url, data=login_data)
    return login_response.json()["Message"] if login_response.json()["Message"] else 'OK'


# 登录并且返回cookies
@frappe.whitelist()
def login_k3cloud(server_url, acctid, user_name, user_password):
    login_url = server_url + "Kingdee.BOS.WebApi.ServicesStub.AuthService.ValidateUser.common.kdsvc"
    login_data={"acctid": acctid, "username": user_name, "password":user_password ,"lcid": 2052}
    login_response = requests.post(url=login_url, data=login_data)
    if login_response.json()["LoginResultType"] == 1:
        # 返回cookies
        return login_response.cookies
    # return login_response


# 增加计量单位
@frappe.whitelist()
def add_uom_from_erp(uom_name):
    doc = frappe.get_doc({'doctype':'SRM UOM' , 'uom_name': uom_name})
    doc.insert()


# 增加物料组
@frappe.whitelist()
def add_material_group_from_erp(material_group, parent_group=None):       
    doc = frappe.get_doc({'doctype':'SRM Material Group' , 'material_group': material_group, 'parent_srm_material_group': parent_group})
    doc.insert()


# 删除创建状态的K3Cloud供应商档案
@frappe.whitelist()
def del_to_k3cloud_supplier(supplier_number):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '供应商主数据'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    else:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    # 调用登录函数
    if login_response:
        # 构建删除接口URL
        del_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Delete.common.kdsvc"
        post_options = { "Numbers": supplier_number }
        post_data = { "FormID": "BD_Supplier", "Data": json.dumps(post_options) }
        del_response = requests.post(url = del_url, data = post_data, cookies = login_response)
        r = json.loads(del_response.text)
        return r


# 写入K3Cloud供应商档案,2023-03-24优化: 
# 1>创建供应商; 2>根据第1步返回的供应商编码去创建联系人档案; 3>根据第1步返回的供应商ID及第2步返回的联系人编码再修改供应商
@frappe.whitelist()
def sync_to_k3cloud_supplier(supplier_docname, orgid = '200', group_nubmer='FT'):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '供应商主数据'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    doc = frappe.get_doc('SRM Supplier', supplier_docname)
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    else:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    # 调用登录函数
    if login_response:
        # 构建保存接口URL
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        # 构建k3Cloud的传参参数，其中{}是从API中复制得来
        post_options = {
                        "Model": {
                            "FSupplierId": 0,
                            "FCreateOrgId": {"FNumber": orgid},             #ERP创建组织
                            "FUseOrgId": {"FNumber": orgid },               #ERP使用组织
                            "FName": doc.supplier_name,       #供应商名称
                            "FShortName": doc.supplier_name,
                            "F_XCD_GYSSSJT": doc.supplier_group,            #2024-05-09广东微电优化: 供应商所属集团必填
                            "FGroup": {
                                "FNumber": group_nubmer       #供应商分组
                            },
                            "FBaseInfo": {
                                "FAddress": doc.default_address if doc.default_address else None,
                                "FFoundDate": get_date_str(doc.company_start_time) if doc.company_start_time else "1900-01-01" ,
                                "FLegalPerson": doc.legalperson if doc.legalperson else "",                # "法人代表",
                                "FRegisterFund": doc.capital_ten_thousand if doc.capital_ten_thousand else 0,
                                "FSOCIALCRECODE": doc.tax_identify if doc.tax_identify else None,             # 统一社会信用代码
                                "FRegisterAddress": doc.company_registered_address if doc.company_registered_address else None,         # "注册地址",
                                "FSupplyClassify": 'CG' if doc.supply_category == '采购' else 'WW' if doc.supply_category == '委外' else 'FW' if doc.supply_category == '服务' else 'ZH',       #供应类别(采购，委外，服务......)
                                "FSupplierClassify": {"FNumber": "01"}, #供应商分类
                                "FCompanyClassify": {
                                    "FNumber": frappe.get_doc('SRM Company Nature', doc.company_nature).fnumber if doc.company_nature else 'GSLL01_SYS'               # 公司类别
                                },
                                "FCompanyNature": {
                                    "FNumber": frappe.get_doc('SRM Company Property', doc.company_property).fnumber if doc.company_property else 'GSXZ001_SYS'                # 公司性质
                                },
                                "FCompanyScale": {
                                    "FNumber": frappe.get_doc('SRM Company Size', doc.company_size).fnumber if doc.company_size else 'GSGM01_SYS'                # 公司规模
                                },
                            },
                            "FBusinessInfo": {
                                "FSettleTypeId": {"FNumber": frappe.get_doc('SRM SettleStyle', doc.settlestyle).fnumber if doc.settlestyle else 'JSFS01_SYS'},                # 结算方式
                                "FVmiBusiness": 0,
                                "FEnableSL": 0
                            },
                            "FFinanceInfo": {
                                "FPayCurrencyId": {
                                    "FNumber": "PRE001" if doc.currency == "CNY" else "PRE007"                # 结算币别 PRE007是美元
                                },
                                "FPayCondition": {
                                    "FNumber": frappe.get_doc('SRM Payment Mode', doc.payment_mode).fnumber if doc.payment_mode else '001'               # 付款条件
                                },
                                # 税分类
                                "FTaxType": {"FNumber": "SFL02_SYS"},
                                # 发票类型
                                "FInvoiceType": "1",
                                # 默认税率                 
                                "FTaxRateId": {"FNUMBER": "SL02_SYS"}
                            },
                            #银行账号信息
                            "FBankInfo": [
                                {
                                    "FBankCode": doc.bank_account if doc.bank_account else None,          # 银行账号
                                    "FBankHolder": doc.company_name if doc.company_name else None,               # 账户名称
                                    "FOpenBankName": doc.bank_name if doc.bank_name else None,                       # 开户银行
                                    "FOpenAddressRec": doc.bank_address if doc.bank_address else None,        # 开户行地址
                                    "FCNAPS": doc.bank_code if doc.bank_code else None,                    # 联行号
                                    "FBankIsDefault": 0
                                }
                            ]
                        }
        }
        # 序列化json.dumps()
        post_data = { "FormID": "BD_Supplier", "Data": json.dumps(post_options) }
        # 1> 同步至K3cloud生成供应商档案
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        r = json.loads(save_response.text)
        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
            # 从ERP返回ID和编码
            supplier_id = r["Result"]["Id"]
            supplier_number = r["Result"]["Number"]
            # 创建联系人Json串,关键词在这"FCompany": {"FNumber": supplier_number},这个supplier_number必须是第1步成功返回的值
            post_options_contact = {
                "Model": {
                    "FCONTACTID": 0,
                    "FName": doc.contact[0].full_name,
                    "Fex": {
                        "FNumber": "SEX01_SYS" if doc.contact[0].gender == "男" else "SEX02_SYS"
                    },
                    "FCompanyType": "BD_Supplier",
                    "FCompany": {"FNumber": supplier_number},
                    "FPost": doc.contact[0].duty if doc.contact[0].duty else "",
                    "FTel": doc.contact[0].phone if doc.contact[0].phone else "",
                    "FMobile": doc.contact[0].mobile_phone if doc.contact[0].mobile_phone else "",
                    "FEmail": doc.contact[0].email_address if doc.contact[0].email_address else "",
                    "FIsDefaultContact": 0,
                    "FIsDefaultConsignee": 0
                }
            }
            # 序列化联系人Json串
            post_data_contact = { "FormID": "BD_CommonContact", "Data": json.dumps(post_options_contact) }
            # 2> 同步至K3cloud生成联系人档案: 注意data传参必须是序列化后联系人Json串
            save_response_contact = requests.post(url = save_url, data = post_data_contact, cookies = login_response)
            # 联系人返回结果
            r_contact = json.loads(save_response_contact.text)
            if r_contact["Result"]["ResponseStatus"]["IsSuccess"] == True:
                # 获取返回的联系人编码
                contact_number = r_contact["Result"]["Number"]
                # 此时有供应商编码和联系人档案编码
                # 创建修改供应商Json串
                post_options_supplier_update = {
                    "Model": {
                        # 非常关键: 这里必须传入第1步返回的Id值,同样是保存接口就起修改的作用
                        "FSupplierId": supplier_id,
                        # 非常关键: "FContactNumber":contact_number, 这个contact_number是第2步成功返回的值
                        "FSupplierContact":[{
                                "FContact":doc.contact[0].full_name,
                                "FContactNumber":contact_number,
                                "FMobile":doc.contact[0].mobile_phone if doc.contact[0].mobile_phone else "",
                                "FPost": doc.contact[0].duty if doc.contact[0].duty else "",
                                "FTel": doc.contact[0].phone if doc.contact[0].phone else "",
                                "FEmail": doc.contact[0].email_address if doc.contact[0].email_address else "",
                                "FCommonContactId":{"FNumber":contact_number}
                            }]
                    }
                }
                # 序列化修改供应商Json串
                post_data_supplier_update = { "FormID": "BD_Supplier", "Data": json.dumps(post_options_supplier_update) }
                # 3> 再次调用保存接口以修改供应商的联系人列表
                save_response_supplier_update = requests.post(url = save_url, data = post_data_supplier_update, cookies = login_response)
                r_supplier_update = json.loads(save_response_supplier_update.text)
                if r_supplier_update["Result"]["ResponseStatus"]["IsSuccess"] == True:
                    return r_supplier_update
                else:
                    frappe.msgprint(r_supplier_update["Result"]["ResponseStatus"]["Errors"][0]["Message"])
            else:
                frappe.msgprint(r_contact["Result"]["ResponseStatus"]["Errors"][0]["Message"])

        else:
            frappe.msgprint(r["Result"]["ResponseStatus"]["Errors"][0]["Message"])
            # return r["Result"]["ResponseStatus"]["Errors"][0]["Message"]
        # return r

# 分配SRM供应商账户
@frappe.whitelist()
def invite_supplier_user(contact, tax_identify):
    # 获取供应商档案name
    for_value = frappe.db.get_list('SRM Supplier',filters={'tax_identify':tax_identify}, fields=['name'], ignore_permissions=True)
    if for_value:
        try:
            # 增加用户
            user = frappe.get_doc(
                    {
                        "doctype": "User",
                        "first_name": contact,
                        "email": contact,
                        "user_type": "System User",
                        "send_welcome_email": 1,    #发送欢迎邮件
                        "module_profile": "SRM Supplier L1",       #默认一个模块分组
                        "roles": [{'parentfield': 'roles', 'parenttype': 'User', 'role': 'SRM Supplier','doctype': 'Has Role'}],    #分配角色
                    }
                ).insert(ignore_permissions=True)
            # 增加数据授权
            add_user_permission('SRM Supplier', for_value[0].name, user.name, ignore_permissions=True)
            # 绑定默认值SRM User Default 2023-02-16取消
            # frappe.get_doc({'doctype': 'SRM User Default', 'user': contact, 'setting_key': 'SRM Supplier', 'setting_value': for_value[0].name}).insert(ignore_permissions=True)
            # 把联系人设置为已分配用户

        except frappe.NameError:
            pass
    else:
        frappe.throw(title='Error', msg='税务识别号无法关联供应商档案,不允许分配用户!')


# 写入K3Cloud采购价目表formid:PUR_PriceCategory, 待完成
@frappe.whitelist()
def sync_to_k3cloud_pricecategory(supplier_number, supplier_name, org='UM01', currency = 'PRE001'):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '采购价目表'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    # 调用登录函数
    if login_response:
        # 构建保存接口URL
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        submit_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Submit.common.kdsvc"
        audit_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Audit.common.kdsvc"
        # 构建k3Cloud的传参参数:尽可能简洁
        post_options = {
                        "Model": {
                            "FCreateOrgId": {
                                "FNumber": org
                            },
                            "FNumber": supplier_number,
                            "FName": supplier_name,
                            "FPriceObject": "A",
                            "FPriceType": "2",
                            "FCurrencyID": {
                                "FNumber": currency
                            },
                            "FSupplierID": {
                                "FNumber": supplier_number
                            },
                            "FDescription": "SRM协同API创建",
                        }
                    }
        # 序列化json.dumps()
        post_data = { "FormID": "PUR_PriceCategory", "Data": json.dumps(post_options) }
        # 同步至K3cloud
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        
        r = json.loads(save_response.text)
        # 保存接口
        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
            number = r["Result"]["Number"]
            submit_post_data = {"FormId":"PUR_PriceCategory","Data":json.dumps({"Numbers": [number] }) }
            submit_response = requests.post(url = submit_url, data = submit_post_data, cookies = login_response)
            submit_r = json.loads(submit_response.text)
            # 提交接口
            if submit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                audit_post_data = {"FormId":"PUR_PriceCategory","Data":json.dumps({"Numbers": [number] }) }
                submit_response = requests.post(url = audit_url, data = audit_post_data, cookies = login_response)
                audit_r = json.loads(submit_response.text)
                # 审核接口
                if audit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                    return r["Result"]["Number"]


# 写入K3Cloud调价表的检查: 供应商是否已经存在, 采购价目表是否有效
@frappe.whitelist()
def validate_before_price_adjustmen(supplier_number):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '调价表'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if login_response:
        view_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.View.common.kdsvc"
        view_options = {"FormId": "BD_Supplier", "data": json.dumps({"Number": supplier_number})}
        view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
        view_res = json.loads(view_response.text)
        if view_res:
            # 供应商编码在云星空存在并且审核未禁用
            if view_res["Result"]["ResponseStatus"]["IsSuccess"] == True and view_res["Result"]["Result"]["DocumentStatus"] == "C" and view_res["Result"]["Result"]["ForbidStatus"] == "A":
                view_options = {"FormId": "PUR_PriceCategory", "data": json.dumps({"Number": supplier_number})}
                view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
                view_res = json.loads(view_response.text)
                if view_res:
                    # 采购价目表编码在云星空存在并且审核未禁用
                    if view_res["Result"]["ResponseStatus"]["IsSuccess"] == True and view_res["Result"]["Result"]["DocumentStatus"] == "C" and view_res["Result"]["Result"]["ForbidStatus"] == "A":
                        return view_res["Result"]["Result"]["Number"]
                    frappe.throw(f"采购价目表编码 {supplier_number} 在云星空不存在或未审核")
            else:
                frappe.throw(f"供应商编码 {supplier_number} 在云星空不存在或未审核")


# 写入K3Cloud调价表: 特殊应用,以ERP供应商编码为价目表关联编码
# 2023-04-13: 增加currency参数
@frappe.whitelist()
def sync_to_k3cloud_price_adjustment(supplier_number, material_number, price, taxprice, taxrate, docname, session_fullname, currency="CNY", creatorid=157024, org="UM01", fname="API写入"):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '调价表'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    # 调用登录函数
    if login_response:
        # 构建保存接口URL
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        # 构建k3Cloud的传参参数，其中{}是从API中复制得来
        post_options = {
            "Model": {
                "FUseOrgId": {
                    "FNumber": org
                },
                "FPurchaseOrgId": {
                    "FNumber": org
                },
                "FCreatorId": {
                    "FUserID": creatorid       #用户demo2:157024
                },
                "FCreateDate": now(),
                "FName": "采购调价表",
                "FPaReason": {
                    "FNumber": "TJYY01_SYS"     #调价原因: TJYY01_SYS[定期询价],TJYY02_SYS[新品询价],TJYY03_SYS[临时询价]
                },
                "FDate": now(),
                "FDescription": f"协同API接口创建,源单号-比价单:{docname}, 创建人:{session_fullname}",
                "F_JLKJ_CGJMBJGLX": "2",        # 价格类型: "2"是[采购], "3"是[委外]
                "F_JLKJ_CGBM": {
                    "FNUMBER": "GL.03"      # 采购部门
                },
                "FPUR_PATENTRY": [
                    {
                        "FAdjustType": "A",     # 调价类型: "A"是[新增]
                        "FPriceListId": {
                            "FNumber": f"{supplier_number}"       # 关联价目表: 九联是以供应商编码创建价目表,特殊规则
                        },
                        "FSupplierId": {
                            "FNumber": f"{supplier_number}"        # 关联供应商
                        },
                        "FCurrencyId": {
                            "FNAME": "人民币" if currency == "CNY" else "美元"
                        },
                        "FMaterialId": {
                            "FNumber": f"{material_number}"         # 物料
                        },
                        "FAfterPrice": price,
                        "FAfterTaxPrice": taxprice,
                        "FAfterTaxRate": taxrate,
                        "FAfterPriceCoefficient": 1.0,
                        "FUpPrice": price,
                        "FDownPrice": price,
                        "FEffectiveDate": now(),
                        "FExpiryDate": "2099-02-20 00:00:00"
                    }
                ]
            }
        }
        # 序列化json.dumps()
        post_data = { "FormID": "PUR_PAT", "Data": json.dumps(post_options) }
        # 同步至K3cloud
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        
        r = json.loads(save_response.text)
        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
            # frappe.msgprint(r["Result"]["Number"])    #调试
            return r["Result"]["Number"]
        else:
            frappe.msgprint(r["Result"]["ResponseStatus"]["Errors"][0]["Message"])
            # return r["Result"]["ResponseStatus"]["Errors"][0]["Message"]


# 写入K3Cloud调价表: 特殊应用,以ERP供应商编码为价目表关联编码
# 2023-06-21: 生成云星空的采购调价单是以同一个供应商的报价生成一个调价单, 所以要先组合明细表的JSON串
@frappe.whitelist()
def sync_to_k3cloud_price_adjustment_more_entrys(docname, pur_patentry, session_fullname, creatorid=157024, org="UM01"):
    acctid = frappe.db.get_all('SRM Demo AcctID', filters={'enable': 1, 'application': '调价表'},fields=['acctid'])
    server_config = frappe.db.get_all('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])
    
    if acctid:
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    if login_response:
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        post_options = {
            "Model": {
                "FUseOrgId": {
                    "FNumber": org
                },
                "FPurchaseOrgId": {
                    "FNumber": org
                },
                "FCreatorId": {
                    "FUserID": creatorid       #2023-09-07修订: 用当前提交用户的mobile_no
                },
                "FCreateDate": now(),
                "FName": "采购调价表",
                "FPaReason": {
                    "FNumber": "TJYY01_SYS"     #调价原因: TJYY01_SYS[定期询价],TJYY02_SYS[新品询价],TJYY03_SYS[临时询价]
                },
                "FDate": now(),
                "FDescription": f"协同API接口创建,源单号-报价单:{docname}-创建人:{session_fullname}",
                "F_JLKJ_CGJMBJGLX": "2",        # 价格类型: "2"是[采购], "3"是[委外]
                "F_JLKJ_CGBM": {
                    "FNUMBER": "GL.03"      # 采购部门
                },
                "FPUR_PATENTRY": pur_patentry,
            }
        }
        # 序列化json.dumps()
        post_data = { "FormID": "PUR_PAT", "Data": json.dumps(post_options) }
        # 同步至K3cloud
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        
        r = json.loads(save_response.text)
        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
            # frappe.msgprint(r["Result"]["Number"])    #调试
            return r["Result"]["Number"]
        else:
            frappe.msgprint(r["Result"]["ResponseStatus"]["Errors"][0]["Message"])
            # return r["Result"]["ResponseStatus"]["Errors"][0]["Message"]


# 修改K3Cloud物料主数据的最小包装MPQ,formid:PUR_CATALOG
# 2023-04-16优化: 根据协同系统物料中的ID直接修改云星空, 取消调用查看API
@frappe.whitelist()
def sync_alter_material_mpq(material_number, mpq=1, moq=0, lt=1, fmaxstock=1):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '物料主数据'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    # 调用登录函数
    if login_response:
        # 这是查看接口url
        # view_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.View.common.kdsvc"
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        # 查看接口的传参有差异
        # view_options = {"FormId": "BD_MATERIAL", "data": json.dumps({"Number": material_number})}
        # view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
        # view_res = json.loads(view_response.text)
        masterid = frappe.get_doc('SRM Material', material_number).fmaterialid
        if masterid:
            save_options = {"FormId": "BD_MATERIAL", "data": json.dumps({"Model": {"FMATERIALID": masterid, "SubHeadEntity1": {"FMaxStock": fmaxstock},"SubHeadEntity4": {"FIncreaseQty": mpq, "FFixLeadTime": lt, "FMinPOQty": moq}}})}
            save_response = requests.post(url=save_url, data=save_options, cookies=login_response)
            save_res = json.loads(save_response.text)
            if save_res["Result"]["ResponseStatus"]["IsSuccess"] == True:
                return save_res
            if save_res["Result"]["ResponseStatus"]["IsSuccess"] == False:
                error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_alter_material_mpq', 'error': save_res["Result"]["ResponseStatus"]["Errors"][0]["Message"]})
                error.insert(ignore_permissions=True)
        else:
            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_alter_material_mpq', 'error': f'{material_number}找不到masterid'})
            error.insert(ignore_permissions=True)


# 写入K3Cloud货源清单formid:PUR_CATALOG
@frappe.whitelist()
def sync_to_k3cloud_source_list(material_number, supplier_number, docname, qrzt, qty, org='UM01'):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '货源清单'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
        
    # 调用登录函数
    if login_response:
        # 构建保存接口URL
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        submit_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Submit.common.kdsvc"
        audit_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Audit.common.kdsvc"
        # 先调用查询接口检索是否已经存在货源清单记录行并且是失效的
        query_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "PUR_CATALOG",
                    "FieldKeys": "FID, FPUR_CATALOGENTRY_FENTRYID, FEntryForbidStatus, FEntryForbidDate, FBillNo ",        # 单据号\行内码\失效标识\业务失效日期
                    "FilterString": f"(FMaterialId.FNumber = '{material_number}' AND FEtySupplierId.FNumber = '{supplier_number}')", # B是已失效,A是未失效,
                    "OrderString": "FENTRYID",
                    "TopRowCount": 0,
                    "StartRow": 0,
                    "Limit": 0,
                    "SubSystemId": ""
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=query_options, cookies=login_response)
        # 将json格式数据转换为字典
        query_res = json.loads(query_response.text)
        # 根据查询接口的返回值判断
        # 已经有记录行:失效记录行?非失效记录行
        if query_res:
            count = 0
            for item in query_res:
                if item[2] == 'B':
                    count += 1
            # 如果有已经失效的记录行,SRM不处理，只提示User
            if count > 0:
                frappe.msgprint(f'已存在失效的记录请到ERP确认')
                return {'message': f'已存在失效的记录请到ERP确认'}
            # 是非失效的记录,SRM先用Save接口把记录行失效:原则上一定只有一个非失效的记录行,这是ERP已经约束的
            else:
                # 构建修改的传参Data
                alter_option = {
                    "IsDeleteEntry": "false",
                    "Model": {  "FID": query_res[0][0],
                                "FDescription": f"协同系统API更新,源单{docname}",
                                "FPUR_CATALOGENTRY": [{ 
                                            "FENTRYID": query_res[0][1],
                                            "FEntryForbidStatus": 'B',
                                            "FEntryForbidDate": now() 
                                        }]  #"FEntryForbiderId": 157024 业务失效人会导致报错
                            }
                }
                # 序列化并且加上formid
                alter_post_data={"formid": "PUR_CATALOG", "data":json.dumps(alter_option)}
                # 调用保存接口
                alter_response=requests.post(url=save_url,data=alter_post_data,cookies=login_response)
                if alter_response:
                    alter_res = json.loads(alter_response.text)
                    # 业务失效成功后开始新增
                    if alter_res["Result"]["ResponseStatus"]["IsSuccess"] == True:
                        # 把原记录行失效后,新增记录行,此处代码行待优化
                        post_options = {
                                    "Model": {
                                        "FPurchaseOrgId": {
                                            "FNumber": org
                                        },
                                        "FName": "货源清单",
                                        "FDate": now(),
                                        "FPUR_CATALOGENTRY": [
                                            {
                                                "FSUPPLYTYPE": "CG",
                                                "FMaterialId": {
                                                    "FNumber": material_number
                                                },
                                                "FUnitID": {
                                                    "FNumber": "Pcs"    #待想办法
                                                },
                                                "FEtySupplierId": {
                                                    "FNumber": supplier_number
                                                },
                                                "FSupplyStatus": "A",
                                                "FEntryEffectiveDate": now(),
                                                "FEntryExpiryDate": "2100-01-01 00:00:00",
                                                "FQuotaOrder": 1,
                                                "FPrimarySupPro": 0,
                                                "FBaseUnitID": {
                                                    "FNumber": "Pcs"
                                                },
                                                "FPayCondition": {
                                                    "FNUMBER": "FKTJ002"
                                                },
                                                "F_JLKJ_QRZT": qrzt,     #确认状态: [1]为[合格], [2]为[限制采购]
                                                "F_JLKJ_SCXGSL": qty        #试产数量
                                            }
                                        ]
                                    }
                                }
                        # 序列化json.dumps()
                        post_data = { "FormID": "PUR_CATALOG", "Data": json.dumps(post_options) }
                        # 同步至K3cloud
                        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
                        
                        r = json.loads(save_response.text)
                        # 保存接口
                        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                            number = r["Result"]["Number"]
                            submit_post_data = {"FormId":"PUR_CATALOG","Data":json.dumps({"Numbers": [number] }) }
                            submit_response = requests.post(url = submit_url, data = submit_post_data, cookies = login_response)
                            submit_r = json.loads(submit_response.text)
                            # 提交接口
                            if submit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                                audit_post_data = {"FormId":"PUR_CATALOG","Data":json.dumps({"Numbers": [number] }) }
                                submit_response = requests.post(url = audit_url, data = audit_post_data, cookies = login_response)
                                audit_r = json.loads(submit_response.text)
                                # 审核接口
                                if audit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                                    frappe.msgprint(r["Result"]["Number"])    #调试
                                else:
                                    frappe.msgprint(audit_r["Result"]["ResponseStatus"]["Errors"][0]["Message"])
                            else:
                                frappe.msgprint(submit_r["Result"]["ResponseStatus"]["Errors"][0]["Message"])

                        else:
                            frappe.msgprint(r["Result"]["ResponseStatus"]["Errors"][0]["Message"])
                    # 业务失效不成功提示
                    else:
                        frappe.msgprint(alter_res["Result"]["ResponseStatus"])
                        # frappe.msgprint('操作已有的记录行业务失效不成功,请联系管理员')
                else:
                    frappe.msgprint('操作已有的记录行业务失效未响应,请联系管理员')
        # 没有货源清单记录,所以直接新增
        else:
            # 构建k3Cloud的传参参数，其中{}是从API中复制得来
            post_options = {
                            "Model": {
                                "FPurchaseOrgId": {
                                    "FNumber": org
                                },
                                "FName": "货源清单",
                                "FDate": now(),
                                "FPUR_CATALOGENTRY": [
                                    {
                                        "FSUPPLYTYPE": "CG",
                                        "FMaterialId": {
                                            "FNumber": material_number
                                        },
                                        "FUnitID": {
                                            "FNumber": "Pcs"    #待想办法
                                        },
                                        "FEtySupplierId": {
                                            "FNumber": supplier_number
                                        },
                                        "FSupplyStatus": "A",
                                        "FEntryEffectiveDate": now(),
                                        "FEntryExpiryDate": "2100-01-01 00:00:00",
                                        "FQuotaOrder": 1,
                                        "FPrimarySupPro": 0,
                                        "FBaseUnitID": {
                                            "FNumber": "Pcs"
                                        },
                                        "FPayCondition": {
                                            "FNUMBER": "FKTJ002"
                                        },
                                        "F_JLKJ_QRZT": qrzt,     #确认状态: [1]为[合格], [2]为[限制采购]
                                        "F_JLKJ_SCXGSL": qty        #试产数量
                                    }
                                ]
                            }
                        }
            # 序列化json.dumps()
            post_data = { "FormID": "PUR_CATALOG", "Data": json.dumps(post_options) }
            # 同步至K3cloud
            save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
            
            r = json.loads(save_response.text)
            # 保存接口
            if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                number = r["Result"]["Number"]
                submit_post_data = {"FormId":"PUR_CATALOG","Data":json.dumps({"Numbers": [number] }) }
                submit_response = requests.post(url = submit_url, data = submit_post_data, cookies = login_response)
                submit_r = json.loads(submit_response.text)
                # 提交接口
                if submit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                    audit_post_data = {"FormId":"PUR_CATALOG","Data":json.dumps({"Numbers": [number] }) }
                    submit_response = requests.post(url = audit_url, data = audit_post_data, cookies = login_response)
                    audit_r = json.loads(submit_response.text)
                    # 审核接口
                    if audit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                        frappe.msgprint(r["Result"]["Number"])    #调试
                    else:
                        frappe.msgprint(audit_r["Result"]["ResponseStatus"]["Errors"][0]["Message"])
                else:
                    frappe.msgprint(submit_r["Result"]["ResponseStatus"]["Errors"][0]["Message"])

            else:
                frappe.msgprint(r["Result"]["ResponseStatus"]["Errors"][0]["Message"])


# 写入K3Cloud采购申请单formid:PUR_Requisition
@frappe.whitelist()
def sync_to_k3cloud_pr(material_number, supplier_number, org='UM01', note = '', entrynote = '', qty=0):
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '采购申请'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    # 调用登录函数
    if login_response:
        # 构建保存接口URL
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        submit_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Submit.common.kdsvc"
        audit_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Audit.common.kdsvc"
        # 构建k3Cloud的传参参数，其中{}是从API中复制得来
        post_options = {
                        "NeedUpDateFields": [],
                        "NeedReturnFields": [],
                        "IsDeleteEntry": "true",
                        "SubSystemId": "",
                        "IsVerifyBaseDataField": "false",
                        "IsEntryBatchFill": "true",
                        "ValidateFlag": "true",
                        "NumberSearch": "true",
                        "IsAutoAdjustField": "false",
                        "InterationFlags": "",
                        "IgnoreInterationFlag": "",
                        "Model": {
                            "FID": 0,
                            "FBillTypeID": {
                                "FNUMBER": "CGSQD98_SYS"        #新物料试产采购申请
                            },
                            "FApplicationDate": now(),
                            "FRequestType": "Material",
                            "FApplicationOrgId": {
                                "FNumber": org
                            },
                            "FApplicationDeptId": {
                                "FNumber": "YJ.09"              #申请部门
                            },
                            "FCurrencyId": {
                                "FNumber": "PRE001"
                            },
                            "FISPRICEEXCLUDETAX": 1,
                            "FNote": 'SRM协同承认单: ' + note,
                            "FExchangeTypeId": {
                                "FNumber": "HLTX01_SYS"
                            },
                            "FIsConvert": 0,
                            "FACCTYPE": "Q",
                            "FMobBillHead": {
                                "FIsMobBill": 0,
                                "FMobIsPending": 0
                            },
                            "FEntity": [
                                {
                                    "FRequireOrgId": {
                                        "FNumber": org
                                    },
                                    "FMaterialId": {
                                        "FNumber": material_number
                                    },
                                    "FMaterialDesc": "",
                                    "FUnitId": {
                                        "FNumber": "Pcs"
                                    },
                                    "FReqQty": qty,
                                    "FApproveQty": qty,
                                    "FPurchaseOrgId": {
                                        "FNumber": org
                                    },
                                    "FSuggestSupplierId": {
                                        "FNumber": supplier_number
                                    },
                                    "FReceiveOrgId": {
                                        "FNumber": org
                                    },
                                    "FPriceUnitId": {
                                        "FNumber": "Pcs"
                                    },
                                    "FPriceUnitQty": qty,
                                    "FREQSTOCKUNITID": {
                                        "FNumber": "Pcs"
                                    },
                                    "FREQSTOCKQTY": qty,
                                    "FLeadTime": 30,
                                    "FEntryNote": supplier_number,
                                    "FEntryNote": entrynote,
                                    "FBaseReqQty": qty,
                                    "FSalUnitID": {
                                        "FNumber": "Pcs"
                                    },
                                    "FSalQty": qty,
                                    "FSalBaseQty": qty,
                                    "FIsVmiBusiness": 0
                                }
                            ]
                        }
                    }
        # 序列化json.dumps()
        post_data = { "FormID": "PUR_Requisition", "Data": json.dumps(post_options) }
        # 同步至K3cloud
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        
        r = json.loads(save_response.text)
        # 保存接口
        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
            number = r["Result"]["Number"]
            submit_post_data = {"FormId":"PUR_Requisition","Data":json.dumps({"Numbers": [number] }) }
            submit_response = requests.post(url = submit_url, data = submit_post_data, cookies = login_response)
            submit_r = json.loads(submit_response.text)
            # 提交接口
            if submit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                audit_post_data = {"FormId":"PUR_Requisition","Data":json.dumps({"Numbers": [number] }) }
                submit_response = requests.post(url = audit_url, data = audit_post_data, cookies = login_response)
                audit_r = json.loads(submit_response.text)
                # 审核接口
                if audit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                    # frappe.msgprint(r["Result"]["Number"])    #调试
                    return r["Result"]["Number"]
                else:
                    frappe.msgprint(audit_r["Result"]["ResponseStatus"]["Errors"][0]["Message"])
            else:
                frappe.msgprint(submit_r["Result"]["ResponseStatus"]["Errors"][0]["Message"])

        else:
            frappe.msgprint(r["Result"]["ResponseStatus"]["Errors"][0]["Message"])


# 从ERP同步物料--One By One
@frappe.whitelist()
def get_material_from_erp(material_number):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        
        post_options = {"data": json.dumps({
                    "FormId": "BD_MATERIAL",
                    "FieldKeys": "FMATERIALID, FErpClsID, FNumber, FName, FSpecification, FBaseUnitId.FName",
                    "FilterString": f" FNumber = '{material_number}' "
                })
        }
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        res = json.loads(query_response.text)
        
        if res:
            fmaterialid, erp_clsid, material_number, material_name, material_model, uom_name = res[0][0], res[0][1], res[0][2], res[0][3], res[0][4], res[0][5]
            if frappe.db.exists("SRM Material", {"material_number": material_number}):
                doc = frappe.get_doc("SRM Material", material_number)
                doc.material_name = res[0][3].strip()
                doc.material_model = res[0][4].strip()
                doc.save(ignore_permissions=True)
                r = '已经存在该编码!只同步名称和规格描述'
            else:
                if not frappe.db.exists('SRM UOM', uom_name):
                    add_uom_from_erp(uom_name)
                doc = frappe.get_doc({'doctype':'SRM Material', 'material_number': material_number, 'material_name': material_name, 'material_model': material_model, 'uom': uom_name, 'fmaterialid': fmaterialid, 'erp_clsid': erp_clsid })
                doc.insert(ignore_permissions=True)
                r = '手动同步成功'
            frappe.db.commit()
            return r
        else:
            frappe.msgprint('未能从ERP获取该编码!请检查录入项FNumber')


# 从ERP同步物料--初始化使用
@frappe.whitelist()
def init_sync_material_from_erp(material_id):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "BD_MATERIAL",
                    "FieldKeys": "FMATERIALID, FErpClsID, FNumber, FName, FSpecification, FBaseUnitId.FName",
                    "FilterString": f" FMATERIALID = {material_id}"
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        
        if res:
            fmaterialid, erp_clsid, material_number, material_name, material_model, uom_name = res[0][0], res[0][1], res[0][2], res[0][3], res[0][4], res[0][5]
            if frappe.db.exists("SRM Material", {"material_number": material_number}):
                doc = frappe.get_doc("SRM Material", frappe.db.exists("SRM Material", {"material_number": material_number}))
                if doc.material_name == material_name.strip() and doc.material_model == material_model.strip():
                    pass
                else:
                    doc.material_name = material_name.strip()
                    doc.material_model = material_model.strip()
                    doc.save(ignore_permissions=True)
            else:
                if not frappe.db.exists('SRM UOM', uom_name):
                    add_uom_from_erp(uom_name)
                doc = frappe.get_doc({'doctype':'SRM Material', 'material_number': material_number, 'material_name': material_name, 'material_model': material_model, 'uom': uom_name, 'fmaterialid': fmaterialid, 'erp_clsid': erp_clsid })
                doc.insert()
                frappe.db.commit()
                # if not frappe.db.exists('SRM Material Group', res[i][3]):
                #     add_material_group_from_erp(res[i][3])
                

# 从ERP获取待同步物料的MaterialID--初始化使用
@frappe.whitelist()
def get_material_id_from_erp(startrow):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        
        post_options = {"data": json.dumps({
                # 2023-04-20: 只获取物料编码, 名称, 规格
                    "FormId": "BD_MATERIAL",
                    "FieldKeys": "FMATERIALID, FNumber, FName, FSpecification, FBaseUnitId.FName, FErpClsID",
                    "FilterString": " FDocumentStatus = 'C' AND FIsPurchase = 1 AND FForbidStatus = 'A' AND FErpClsID NOT in (5, 9)",
                    "StartRow": startrow,
                    "Limit": 2000
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        
        if res:
            return res


# 2023-04-20: 从ERP先把全部物料的基本信息拿到并且存放在一个数组中, 再与SRM的物料主数据进行匹配, 是否需要更新物料名称, 规格型号
@frappe.whitelist()
def new_day_sync_material_from_erp():
    # 初始化起始行为0
    startrow = 0
    # 初始化接收列表为空
    material_ls = []
    # 循环:百万条内
    while startrow < 1000000:
        # 获取limit:2000条返回行
        r = get_material_id_from_erp(startrow)
        # 判断有返回由往接收列表中抛并且把起始行变量+2000(为什么是+2000,跟limit匹配)
        if r and len(r) > 0:
            material_ls.append(r)
            startrow += 2000
        # 如果没有返回则跳出循环
        else:
            break
    # 有接收结果的处理规则: 在接收结果中2层循环
    if len(material_ls) > 0:
        for i in material_ls:
            for m in i:
                # 如果当前物料编码已经存在SRM中并且名称和规格都相同则pass, 否则就更新名称和规格并保存
                # 2023-07-28修正: 取消参数cache=True
                if frappe.db.exists("SRM Material", m[1]):
                    doc = frappe.get_doc("SRM Material", frappe.db.exists("SRM Material", m[1], cache=True))
                    if doc.material_name == m[2].strip() and doc.material_model == m[3].strip():
                        pass
                    else:
                        doc.material_name = m[2].strip()
                        doc.material_model = m[3].strip()
                        doc.save(ignore_permissions=True)
                # 如果当前物料编码不存在于SRM中, 新增之
                else:
                    if not frappe.db.exists('SRM UOM', m[4]):
                        add_uom_from_erp(m[4])
                    doc = frappe.get_doc({'doctype':'SRM Material', 'material_number': m[1], 'material_name': m[2], 'material_model': m[3], 'uom': m[4], 'fmaterialid': m[0], 'erp_clsid': m[5] })
                    doc.insert(ignore_permissions=True)
        frappe.db.commit()


# 从ERP同步供应商--One By One
@frappe.whitelist()
def get_supplier_from_erp(supplier_number):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        
        post_options = {"data": json.dumps({
                # FilterString中有个小细节:{变量名}一定要在引号里面!坑！坑！坑！
                    "FormId": "BD_Supplier",
                    "FieldKeys": "FGroup, FNumber, FName, FCorrespondOrgId.FNumber, FSOCIALCRECODE, FContact, FEMail, FTel, FMobile",
                    "FilterString": f" FNumber = '{supplier_number}' "
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        
        if res:
            group, supplier_number, supplier_name, org, tax_identify, full_name, email_address, phone, mobile_phone = res[0][0], res[0][1], res[0][2], res[0][3], res[0][4], res[0][5], res[0][6], res[0][7], res[0][8]
            if frappe.db.exists("SRM Supplier", {"supplier_number": supplier_number}):
                frappe.msgprint('已经存在该编码!')
            elif email_address:
                doc = frappe.get_doc({
                                        'doctype':'SRM Supplier', 
                                        'supplier_number': supplier_number, 
                                        'supplier_name': supplier_name, 
                                        'org': org, 
                                        'tax_identify': tax_identify,
                                        'contact': [{
                                            'full_name': full_name if full_name else 'A',
                                            'email_address': email_address,
                                            'phone': phone if phone else '',
                                            'mobile_phone': mobile_phone if mobile_phone else ''
                                        }]
                })
                doc.insert()
                frappe.db.commit()
            else:
                doc = frappe.get_doc({
                                        'doctype':'SRM Supplier', 
                                        'supplier_number': supplier_number, 
                                        'supplier_name': supplier_name, 
                                        'org': org, 
                                        'tax_identify': tax_identify,
                })
                doc.insert()
                frappe.db.commit()                
        else:
            frappe.msgprint('未能从ERP获取该编码!请检查录入项FNumber')


# 从ERP获取待同步供应商的SupplierID--初始化使用
@frappe.whitelist()
def get_supplier_id_from_erp(startrow):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "BD_Supplier",
                    "FieldKeys": "FNumber",          # FSupplierId, FName
                    "FilterString": " FDocumentStatus = 'C' AND FForbidStatus = 'A'",
                    "StartRow": startrow,
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        
        if res:
            return res


# 对账单获取明细: 从ERP同步采购入库单和采购退库单: 给定条件为供应商及日期范围 2023-01-31
# 2025-05-06修订: 增加参数purchase_org
@frappe.whitelist()
def sync_purchaseinstock_from_erp(date_from, date_to, supplier_number, purchase_org):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        # 2025-05-06修订: 接口地址修订及组织代码识别修订JLKJ.R.SCM.API.getSTKCloseDate,JLKJ.R.SCM.common.kdsvc
        get_closedate_url = server_config[0].server_url + "HZKD.API.STKCLOSEDATE.API.getSTKCloseDate,HZKD.API.STKCLOSEDATE.common.kdsvc"
        get_closedate_response = requests.post(url=get_closedate_url, cookies=login_response)
        get_closedate_res = json.loads(get_closedate_response.text)
        for org in get_closedate_res:
            if org["ORGNUMBER"] == purchase_org and org["CLOSEDATE"] < date_to:
                frappe.throw(f'对账截止日期{date_to} 大于 ERP当前的关账日期 {org["CLOSEDATE"]} 对账明细可能还有调整')
            elif org["ORGNUMBER"] == purchase_org and org["CLOSEDATE"] >= date_to:
                query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
                post_options = {"data": json.dumps({
                            "FormId": "STK_InStock",
                            # 2023-12-09调整: 增加FEntryTaxRate, FEntryTaxAmount, FPrice, FAmount
                            
                            "FieldKeys": "'in-of-warehouse',FDATE,FPOORDERNO,FMATERIALID.FNUMBER,FMaterialName,FUOM,FUnitID.FNAME,FRealQty,FTaxPrice,FAllAmount, FEntryTaxRate, FEntryTaxAmount, FPrice, FAmount",
                            # 2025-05-06修订: 增加组织为过滤条件
                            "FilterString": f"FDATE >= '{date_from}' AND FDATE <= '{date_to}' AND FSUPPLIERID.FNUMBER = '{supplier_number}' AND FDocumentStatus = 'C' AND FPurchaseOrgId.FNumber = '{purchase_org}' ", # C是已审核,A是未提交,
                            "OrderString": "FDATE",
                            "TopRowCount": 0,
                            "StartRow": 0,
                            "Limit": 0,
                            "SubSystemId": ""
                        })
                }
                query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
                # 将json格式数据转换为字典,获取入库明细
                res_in = json.loads(query_response.text)
                # if res_in:
                # 以下是查询退料的数据: FDocumentStatus=C是已审核,A是未提交; FMRTYPE = 'B'是[库存退料],A是[检验退料]
                post_options_return = {"data": json.dumps({
                        "FormId": "PUR_MRB",
                        # 2023-12-09调整: 增加FEntryTaxRate, FEntryTaxAmount, FPrice, FAmount
                        "FieldKeys": "'out-of-warehouse',FDATE,FORDERNO,FMATERIALID.FNUMBER,FMaterialName,FMateriaModel ,FUnitID.FNAME,FRMREALQTY,FTaxPrice,FAllAmount, FEntryTaxRate, FEntryTaxAmount, FPrice, FAmount",
                        # 2025-05-06修订: 增加组织为过滤条件
                        "FilterString": f"FDATE >= '{date_from}' AND FDATE <= '{date_to}' AND FSUPPLIERID.FNUMBER = '{supplier_number}' AND FDocumentStatus = 'C' AND FMRTYPE = 'B' AND FPurchaseOrgId.FNumber = '{purchase_org}'", 
                        "OrderString": "",
                        "Limit": 1000
                    })
                }
                query_response_return = requests.post(url=query_url, data=post_options_return, cookies=login_response)
                # 获取退库明细
                res_return = json.loads(query_response_return.text)
                if res_in and res_return:
                    # 数据纵向合并
                    import numpy as np
                    res = np.vstack((res_in, res_return))
                    # print(res)
                    return res
                elif res_in and not res_return:
                    return res_in
                elif not res_in and res_return:
                    return res_return
                else:
                    frappe.throw(f'没有对账数据请检查{server_config[0].server_url}')
  

# 2025-05-06注释掉
# def sync_purchaseinstock_from_erp(date_from, date_to, supplier_number):
#     server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
#     login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

#     if login_response:
#         get_closedate_url = server_config[0].server_url + "JLKJ.R.SCM.API.getSTKCloseDate,JLKJ.R.SCM.common.kdsvc"
#         get_closedate_response = requests.post(url=get_closedate_url, cookies=login_response)
#         get_closedate_res = json.loads(get_closedate_response.text)
#         for org in get_closedate_res:
#             if org["ORGNUMBER"] == 'UM01' and org["CLOSEDATE"] < date_to:
#                 frappe.throw(f'对账截止日期{date_to} 大于 ERP当前的关账日期 {org["CLOSEDATE"]} 对账明细可能还有调整')
#             elif org["ORGNUMBER"] == 'UM01' and org["CLOSEDATE"] >= date_to:
#                 query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
#                 post_options = {"data": json.dumps({
#                             "FormId": "STK_InStock",
#                             # 2023-12-09调整: 增加FEntryTaxRate, FEntryTaxAmount, FPrice, FAmount
#                             "FieldKeys": "'in-of-warehouse',FDATE,FPOORDERNO,FMATERIALID.FNUMBER,FMaterialName,FUOM,FUnitID.FNAME,FRealQty,FTaxPrice,FAllAmount, FEntryTaxRate, FEntryTaxAmount, FPrice, FAmount",
#                             "FilterString": f"FDATE >= '{date_from}' AND FDATE <= '{date_to}' AND FSUPPLIERID.FNUMBER = '{supplier_number}' AND FDocumentStatus = 'C' ", # C是已审核,A是未提交,
#                             "OrderString": "FDATE",
#                             "TopRowCount": 0,
#                             "StartRow": 0,
#                             "Limit": 0,
#                             "SubSystemId": ""
#                         })
#                 }
#                 query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
#                 # 将json格式数据转换为字典,获取入库明细
#                 res_in = json.loads(query_response.text)
#                 # if res_in:
#                 # 以下是查询退料的数据: FDocumentStatus=C是已审核,A是未提交; FMRTYPE = 'B'是[库存退料],A是[检验退料]
#                 post_options_return = {"data": json.dumps({
#                         "FormId": "PUR_MRB",
#                         # 2023-12-09调整: 增加FEntryTaxRate, FEntryTaxAmount, FPrice, FAmount
#                         "FieldKeys": "'out-of-warehouse',FDATE,FORDERNO,FMATERIALID.FNUMBER,FMaterialName,FMateriaModel ,FUnitID.FNAME,FRMREALQTY,FTaxPrice,FAllAmount, FEntryTaxRate, FEntryTaxAmount, FPrice, FAmount",
#                         "FilterString": f"FDATE >= '{date_from}' AND FDATE <= '{date_to}' AND FSUPPLIERID.FNUMBER = '{supplier_number}' AND FDocumentStatus = 'C' AND FMRTYPE = 'B' ", 
#                         "OrderString": "",
#                         "Limit": 1000
#                     })
#                 }
#                 query_response_return = requests.post(url=query_url, data=post_options_return, cookies=login_response)
#                 # 获取退库明细
#                 res_return = json.loads(query_response_return.text)
#                 if res_in and res_return:
#                     # 数据纵向合并
#                     import numpy as np
#                     res = np.vstack((res_in, res_return))
#                     # print(res)
#                     return res
#                 elif res_in and not res_return:
#                     return res_in
#                 elif not res_in and res_return:
#                     return res_return
#                 else:
#                     frappe.throw(f'没有对账数据请检查{server_config[0].server_url}')
  

# 写入K3Cloud[供应商协同采购回复(九联)] 2023-01-30 2023-02-19待取消
@frappe.whitelist()
def sync_to_k3cloud_plan_of_answer1(entry_array, orgid = 'UM01', group_nubmer='01'):
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    # 调用登录函数
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if login_response:
        # 构建保存接口URL
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        # 构建k3Cloud的传参参数，其中{}是从API中复制得来
        fentity = []
        # frappe.msgprint(entry_array)
        entry_array = json.loads(entry_array)
        for i in entry_array:
            if i["fpoentryid"] and i["fexinstockdate"]:
                fentity.append({
                            "FEntryID": 0,
                            "FPOENTRYID": i["fpoentryid"],
                            "FPOBILLNO": "",
                            "FMATERIALLID": {
                                "FNUMBER": ""
                            },
                            "FEXINSTOCKDATE": i["fexinstockdate"],
                            "FQty": i["fqty"]
                        })
        if len(fentity) > 0:
            post_options = {           
                "Model": { "FEntity": fentity }
            }
        # 序列化json.dumps()
        post_data = { "FormID": "JLKJ_GYSXTCGHF", "Data": json.dumps(post_options) }
        # 同步至K3cloud
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        r = json.loads(save_response.text)
        return r


# 从ERP同步采购PO待作废
# 多分录的待完善(08.15已完善,如果已经同步的内容出现更新的处理没完善)
@frappe.whitelist()
def sync_purchaseorder_from_erp_old():
    server_config = frappe.db.get_list('Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        time_add = datetime.timedelta(minutes=5)
        current_time = datetime.datetime.now()
        filter_datetime = (current_time-time_add).strftime("%Y-%m-%d %H:%M:%S")
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "PUR_PurchaseOrder",
                    "FieldKeys": "FBillNo, FDate, FPurchaseOrgId.FName, FSupplierId.FNumber, FPurchaseDeptId.FName, FPurchaserId.FName, FPayConditionId, FMaterialId.FNumber, FUnitId.FName, FQty, FDeliveryDate, FPrice, FEntryAmount, FEntryTaxRate, FTaxPrice, FEntryTaxAmount, FAllAmount, FID, FPOOrderEntry_FEntryID, FPOOrderEntry_FSEQ",        # FieldKeys是关键，这里也会经常有变化
                    "FilterString": f"(FApproveDate >= '{filter_datetime}' OR FModifyDate >= '{filter_datetime}') AND FDocumentStatus = 'C' ", # C是已审核,A是未提交,
                    "OrderString": "FBillNo",
                    "TopRowCount": 0,
                    "StartRow": 0,
                    "Limit": 0,
                    "SubSystemId": ""
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        if res:          
            for i in range(len(res)):
                doc_exists = frappe.get_list('SRM Purchase Order', filters = { 'erp_billno': res[i][0]})
                if doc_exists:
                    doc_sub_exists = frappe.get_list('Purchase Order Sub', filters = { 'fpoorderentry_fentryid': res[i][18] })
                    if doc_sub_exists:
                        pass
                    else:
                        doc = frappe.get_doc('SRM Purchase Order', res[i][0])
                        doc.append("purchase_order_sub", {
                            'material_number': res[i][7],
                            'uom': res[i][8],
                            'qty': res[i][9],
                            'request_date': res[i][10],
                            'price': res[i][11],
                            'amount': res[i][12],
                            'tax_rate': res[i][13],
                            'tax_price': res[i][14],
                            'tax_amount': res[i][15],
                            'all_amount': res[i][16],
                            'fpoorderentry_fentryid': res[i][18]
                            # 'fpoorderentry_fseq': res[i][19]                        
                        }).insert()
                else:
                    doc = frappe.get_doc({
                                        'doctype': 'SRM Purchase Order', 
                                        'erp_billno': res[i][0], 
                                        'date': res[i][1],
                                        'purchaser': res[i][2],  
                                        'srm_supplier': res[i][3],
                                        'purchase_department': res[i][4],
                                        'purchase_person': res[i][5],
                                        'purchase_payment': res[i][6],
                                        'fpoorder_fid': res[i][17],
                                        'purchase_order_sub': [{
                                                'material_number': res[i][7],
                                                'uom': res[i][8],
                                                'qty': res[i][9],
                                                'request_date': res[i][10],
                                                'price': res[i][11],
                                                'amount': res[i][12],
                                                'tax_rate': res[i][13],
                                                'tax_price': res[i][14],
                                                'tax_amount': res[i][15],
                                                'all_amount': res[i][16],
                                                'fpoorderentry_fentryid': res[i][18]
                                                # 'fpoorderentry_fseq': res[i][19]
                                            }]
                                        }).insert()
                frappe.db.commit()


# 从ERP同步采购PO变更单 待作废
@frappe.whitelist()
def sync_purchaseorderchange_from_erp_old():
    server_config = frappe.db.get_list('Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        time_add = datetime.timedelta(minutes=6)
        current_time = datetime.datetime.now()
        filter_datetime = (current_time-time_add).strftime("%Y-%m-%d %H:%M:%S")
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "PUR_POChange",
                    "FieldKeys": "FOFBILLNO,fid,FMATERIALID.fnumber,FCHANGETYPE,FOPRICE,FNPRICE,FOQTY,FNQTY,FUNITID.fname,FODELIVERYDATE,FNDELIVERYDATE",        # FieldKeys是关键，这里也会经常有变化
                    "FilterString": f"(FApproveDate >= '{filter_datetime}' OR FModifyDate >= '{filter_datetime}') AND FDocumentStatus = 'C' ", # C是已审核,A是未提交,
                    "OrderString": "FOFBILLNO",
                    "TopRowCount": 0,
                    "StartRow": 0,
                    "Limit": 0,
                    "SubSystemId": ""
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        if res:          
            for i in range(len(res)):
                doc_exists = frappe.get_list('SRM Purchase Order', filters = { 'erp_billno': res[i][0]})
                if doc_exists:
                    doc_sub_exists = frappe.get_list('Purchase Order Sub Change', filters = { 'purchaseorderchange_id': res[i][1] })
                    if doc_sub_exists:
                        pass
                    else:
                        doc = frappe.get_doc('SRM Purchase Order', res[i][0])
                        doc.append("order_changes", {
                            #'change_type': res[i][3],
                            'material_number': res[i][2],
                            'qty': res[i][6],
                            'after_change_qty': res[i][7],
                            'uom': res[i][8],
                            'request_date': res[i][9],
                            'after_change_request_date': res[i][10],
                            'price': res[i][4],
                            'after_change_price': res[i][5],
                            'purchaseorderchange_id': res[i][1]
                            # 'fpoorderentry_fseq': res[i][19]                        
                        }).insert()
                frappe.db.commit()

# 从ERP同步采购收料单 待作废
@frappe.whitelist()
def sync_purchasereceive_from_erp_old():
    server_config = frappe.db.get_list('Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        time_add = datetime.timedelta(minutes=6)
        current_time = datetime.datetime.now()
        filter_datetime = (current_time-time_add).strftime("%Y-%m-%d %H:%M:%S")
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "PUR_ReceiveBill",
                    "FieldKeys": "FBILLNO,FPURORGID.FNAME,FPURDEPTID.FNAME,FSUPPLIERID.FNumber,FDATE,FPURCHASERID.FNAME,FMATERIALID.FNUMBER,FUNITID.FNUMBER,FPREDELIVERYDATE,FACTRECEIVEQTY,FSUPDELQTY,FREJECTQTY,FREJECTREASON,FSRCBILLNO,FSRCENTRYID,FSTOCKID.FNUMBER,FStockLocId,FDetailEntity_FEntryID,FDetailEntity_FSEQ",        # FieldKeys是关键，这里也会经常有变化
                    "FilterString": f"(FApproveDate >= '{filter_datetime}' OR FModifyDate >= '{filter_datetime}') AND FDocumentStatus = 'C' ", # C是已审核,A是未提交,
                    "OrderString": "FBILLNO",
                    "TopRowCount": 0,
                    "StartRow": 0,
                    "Limit": 0,
                    "SubSystemId": ""
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        if res:          
            for i in range(len(res)):
                doc_exists = frappe.get_list('SRM Purchase Receive', filters = { 'erp_billno': res[i][0]})
                if doc_exists:
                    doc_sub_exists = frappe.get_list('SRM Purchase Receive Sub', filters = { 'sub_fid': res[i][17]})
                    if doc_sub_exists:
                        pass
                    else:
                        doc = frappe.get_doc('SRM Purchase Receive', res[i][0])
                        doc.append("purchase_receive_sub", {
                            'material_number': res[i][6],
                            'uom': res[i][7],
                            'pre_delivery_date': res[i][8],
                            'sup_del_qty': res[i][10],
                            'act_receive_qty': res[i][9],
                            'reject_qty': res[i][11],
                            'reject_reason': res[i][12],
                            'sub_fid': res[i][17],
                            'stock_number': res[i][15],
                            'stock_locid': res[i][16],
                            'erp_enid': res[i][18]                       
                        }).insert()
                else:
                    doc = frappe.get_doc({
                                        'doctype': 'SRM Purchase Receive', 
                                        'erp_billno': res[i][0],
                                        'purchaser': res[i][1],
                                        'srm_supplier': res[i][3],  
                                        'date': res[i][4],
                                        'purchase_department': res[i][2],
                                        'purchase_person': res[i][5],
                                        'erp_fids': res[i][13],
                                        'purchase_receive_sub': [{
                                                'material_number': res[i][6],
                                                'uom': res[i][7],
                                                'pre_delivery_date': res[i][8],
                                                'sup_del_qty': res[i][10],
                                                'act_receive_qty': res[i][9],
                                                'reject_qty': res[i][11],
                                                'reject_reason': res[i][12],
                                                'sub_fid': res[i][17],
                                                'stock_number': res[i][15],
                                                'stock_locid': res[i][16],
                                                'erp_enid': res[i][18]
                                            }]
                                        }).insert()
                frappe.db.commit()                
      

# 从ERP同步采购退料单 待作废
@frappe.whitelist()
def sync_purchasereceivereturn_from_erp_0ld():
    server_config = frappe.db.get_list('Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        time_add = datetime.timedelta(minutes=6)
        current_time = datetime.datetime.now()
        filter_datetime = (current_time-time_add).strftime("%Y-%m-%d %H:%M:%S")
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "PUR_MRB",
                    "FieldKeys": "FBILLNO,FSRCBILLNO,FORDERNO,FPURCHASEORGID.FNAME,FSUPPLIERID.FNUMBER,FDATE,FMRMODE,FPURCHASEDEPTID.FNAME,FPURCHASERID.FNAME,FMATERIALID.FNUMBER,FSTOCKID.FNUMBER,FSTOCKLOCID,FPRICEUNITID.FNUMBER,FCARRYQTY,FRMREALQTY,FREPLENISHQTY,FKEAPAMTQTY,FPOORDERENTRYID,FPRICE,FAMOUNT,FENTRYTAXRATE,FTAXPRICE,FALLAMOUNT,FPURMRBENTRY_FEntryID,FPURMRBENTRY_FSEQ",        # FieldKeys是关键，这里也会经常有变化
                    "FilterString": f"(FApproveDate >= '{filter_datetime}' OR FModifyDate >= '{filter_datetime}') AND FDocumentStatus = 'C' AND FMRTYPE = 'A' ", # C是已审核,A是未提交,
                    "OrderString": "FBILLNO",
                    "TopRowCount": 0,
                    "StartRow": 0,
                    "Limit": 0,
                    "SubSystemId": ""
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        if res:          
            for i in range(len(res)):
                doc_exists = frappe.get_list('SRM Purchase Receive Return', filters = { 'erp_billno': res[i][0]})
                if doc_exists:
                    doc_sub_exists = frappe.get_list('Purchase Receive Return Sub', filters = { 'sub_fid': res[i][23] })
                    if doc_sub_exists:
                        pass
                    else:
                        doc = frappe.get_doc('SRM Purchase Receive Return', res[i][0])
                        doc.append("subs", {
                            'material_number': res[i][9],
                            'stock_id': res[i][10],
                            'stock_loc_id': res[i][11],
                            'uom': res[i][12],
                            'carry_qty': res[i][13],
                            'rmreal_qty': res[i][14],
                            'replenish_qty': res[i][15],
                            'keapamt_qty': res[i][16],
                            'sub_fid': res[i][23],
                            'price': res[i][18],
                            'amount': res[i][19],
                            'tax_rate': res[i][20],
                            'tax_price': res[i][21],
                            'tax_amount': res[i][22]                       
                        }).insert()
                else:
                    fmr_modes=""
                    if res[i][6] == "A":
                        fmr_modes='退料补料'
                    if res[i][6] == "B":
                        fmr_modes='退料并扣款'
                    doc = frappe.get_doc({
                                        'doctype': 'SRM Purchase Receive Return', 
                                        'erp_billno': res[i][0], 
                                        'srcbillno': res[i][1],
                                        'erp_fid': res[i][2],
                                        'purchaser': res[i][3],
                                        'srm_supplier': res[i][4],  
                                        'date': res[i][5],
                                        'fmr_mode': fmr_modes,
                                        'purchase_department': res[i][7],
                                        'purchase_person': res[i][8],
                                        'subs': [{
                                                'material_number': res[i][9],
                                                'stock_id': res[i][10],
                                                'stock_loc_id': res[i][11],
                                                'uom': res[i][12],
                                                'carry_qty': res[i][13],
                                                'rmreal_qty': res[i][14],
                                                'replenish_qty': res[i][15],
                                                'keapamt_qty': res[i][16],
                                                'sub_fid': res[i][23],
                                                'price': res[i][18],
                                                'amount': res[i][19],
                                                'tax_rate': res[i][20],
                                                'tax_price': res[i][21],
                                                'tax_amount': res[i][22]
                                            }]
                                        }).insert()                    
                frappe.db.commit()

# 作废: 从ERP同步采购交期回复表-采购员--2022-11-12
@frappe.whitelist()
def sync_supply_plan_from_erp_old():
    doc_name = frappe.db.get_list(
        "ERP Sync Config",
        filters={'fname': '采购交期回复表', 'enable': 1},
        fields=['name']
    )
    # 增加一个if判断,避免sync = K3CloudSync(doc_name[0].name)时 IndexError: list index out of range
    if doc_name:
        sync = K3CloudSync(doc_name[0].name)
        sync.sync()


# 从ERP获取待同步[采购交期回复表-采购员]的FID 待作废 2023-03-08
@frappe.whitelist()
def get_fid_from_erp():
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "JLKJ_CHGFJHCGY",
                    "FieldKeys": "FID",
                    "FilterString": " FISNEW = 1 ", # 只同步最新的,
                    "StartRow": 0,
                    "OrderString": "FID"
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        # 判断res是否有效
        if res:
            return res


# 从ERP同步采购交期回复表-采购员  待作废 2023-03-08
@frappe.whitelist()
def sync_supply_plan_from_erp():
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "JLKJ_CHGFJHCGY",
                    "FieldKeys": "FID, F_JLKJ_Entity_FENTRYID, FBillNo, FPurchaserId.FName, F_JLKJ_CreatorId.FName, FISNEW, F_JLKJ_CreateDate, FPOENTRYID, FSUPPLIERID.FNumber, FSUPPLIERID.FName, FPOBILLNO, FMATERIALLID.FNumber, F_JLKJ_BaseProperty1, FMATERIALNAME, FMATERIALSPEC, FUnitID.FName, FPOQty, FINQty, FInspectQty, FText, Fsatisfy, FBillneedDate, FAssignQty, FPurchaserId.FName, FLeadTime, F_JLKJ_BaseProperty",
                    "FilterString": " FISNEW = 1 AND FAssignQty >0 AND FBillneedDate > '2023-01-01' ", # 只同步最新的,
                    "Limit": 0,
                    "OrderString": "FID, FSUPPLIERID.FNumber"
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        # 判断res是否有效
        if res:
            for i in range(len(res)):
                # res中的FID存在SRM Supply Plan
                if frappe.db.exists("SRM Supply Plan", {"fid": res[i][0]}, cache=True):
                    # FID存在判断分录行已存在
                    if frappe.db.exists("SRM Supply Plan OrderItem",{"fentryid": res[i][1]}) or res[i][22] <= 0:
                        pass
                    # FID存在判断分录行不存在
                    else:
                        #新增分录行
                        doc_exists = frappe.get_list("SRM Supply Plan", filters={'fid': res[i][0]})
                        doc_exist = frappe.get_doc("SRM Supply Plan", doc_exists[0].name)
                        # doc_exist对象有append方法,增加子表行记录
                        # 增加行记录之前判断供应商编码是否在SRM中存在并且一致,不一致的就跳过
                        if frappe.db.exists("SRM Supplier", res[i][8]):
                            doc_exist.append('orderitem', {
                                'fentryid': res[i][1],                  # 行内码
                                'po_entryid': res[i][7],                # 采购订单行内码
                                'supplier_number': res[i][8],           # 供应商编码
                                'supplier_name': res[i][9].strip(),             # 供应商名称
                                'po_billno': res[i][10],                 # 采购订单号
                                'material_number': res[i][11],           # 物料编码
                                'old_material_number': res[i][12],       # 旧物料编码
                                'material_name': res[i][13].strip(),             # 物料名称
                                'material_model': res[i][14].strip(),            # 规格型号
                                'uom_name': res[i][15],
                                'order_qty': res[i][16],
                                'stock_qty': res[i][17],
                                'inspect_qty': res[i][18],
                                'description': res[i][19].strip(),              #备注
                                'fsatisfy': res[i][20],                 #满足需求
                                'need_date': res[i][21],                #需求日期
                                'assign_qty': res[i][22],               #分配数量
                                'purchase_person': res[i][23],          #采购员
                                'lt': res[i][24],
                                'mpq': res[i][25]
                            }).insert(ignore_permissions=True)
                        elif frappe.db.exists("SRM Supplier", {"supplier_number": res[i][8]}):
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_supply_plan_from_erp', 'error': f'ERP的供应商编码{res[i][8]}与Name不一致'})
                            error.insert(ignore_permissions=True)
                        else:
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_supply_plan_from_erp', 'error': f'ERP的供应商编码{res[i][8]}不存在'})
                            error.insert(ignore_permissions=True)
                # res中的FID不存在SRM Supply Plan
                else:
                    # 舍去分配数量为0的记录行
                    if res[i][22] <= 0:
                        pass
                    else:
                        # 增加行记录之前判断供应商编码是否在SRM中存在并且一致,不一致的就跳过
                        if frappe.db.exists("SRM Supplier", res[i][8]):
                            doc = frappe.get_doc({
                                'doctype': 'SRM Supply Plan', 
                                'fid': f'{res[i][0]}',
                                'fbillno': res[i][2],
                                'buyer': res[i][3],
                                'creatorer': res[i][4],
                                'is_last': res[i][5],
                                'createdate': res[i][6],
                                'orderitem': [{
                                    'fentryid': res[i][1],                  # 行内码
                                    'po_entryid': res[i][7],                # 采购订单行内码
                                    'supplier_number': res[i][8],           # 供应商编码
                                    'supplier_name': res[i][9].strip(),             # 供应商名称
                                    'po_billno': res[i][10],                 # 采购订单号
                                    'material_number': res[i][11],           # 物料编码
                                    'old_material_number': res[i][12],       # 旧物料编码
                                    'material_name': res[i][13].strip(),             # 物料名称
                                    'material_model': res[i][14].strip(),            # 规格型号
                                    'uom_name': res[i][15],
                                    'order_qty': res[i][16],
                                    'stock_qty': res[i][17],
                                    'inspect_qty': res[i][18],
                                    'description': res[i][19].strip(),              #备注
                                    'fsatisfy': res[i][20],                 #满足需求
                                    'need_date': res[i][21],                #需求日期
                                    'assign_qty': res[i][22],               #分配数量
                                    'purchase_person': res[i][23],          #采购员
                                    'lt': res[i][24],
                                    'mpq': res[i][25]
                                }]
                            })
                            # doc.append("SRM Supply Plan OrderItem", {})新增时不支持该append,有错误提示:AttributeError: 'NoneType' object has no attribute 'options'
                            doc.insert(ignore_permissions=True)
                        elif frappe.db.exists("SRM Supplier", {"supplier_number": res[i][8]}):
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_supply_plan_from_erp', 'error': f'ERP的供应商编码{res[i][8]}与Name不一致'})
                            error.insert(ignore_permissions=True)
                        else:
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_supply_plan_from_erp', 'error': f'ERP的供应商编码{res[i][8]}不存在'})
                            error.insert(ignore_permissions=True)
                frappe.db.commit()
        else:
            frappe.throw('ERP没有符合最新的数据返回')

# 从ERP同步采购交期回复表-采购员 安排后台事务 2023-03-08 待作废 2023-03-08
@frappe.whitelist()
def schedule_sync_supply_plan_from_erp():
    r = get_fid_from_erp()
    for i in r:
        if frappe.db.exists("SRM Supply Plan", {"fid": i[0]}, cache=True):
            pass
        elif not frappe.db.exists("SRM Supply Plan", {"fid": i[0]}, cache=True):
            sync_supply_plan_from_erp(fid = i[0])

# 写入K3Cloud价目表---2023-02-20待作废
@frappe.whitelist()
def sync_to_k3cloud_price(supplier_number, material_number, uom, price, taxprice, taxrate, docname, org="100", fname="API写入"):
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    # 调用登录函数
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        # 构建保存接口URL
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        # 构建k3Cloud的传参参数，其中{}是从API中复制得来
        post_options = {
                        "Creator": "",
                        "NeedUpDateFields": [],
                        "NeedReturnFields": [],
                        "IsDeleteEntry": "true",
                        "SubSystemId": "",
                        "IsVerifyBaseDataField": "false",
                        "IsEntryBatchFill": "true",
                        "ValidateFlag": "true",
                        "NumberSearch": "true",
                        "InterationFlags": "",
                        "Model": {
                            "FID": 0,
                            "FCreateOrgId": {
                                "FNumber": org          #创建组织
                            },
                            "FName": fname,             #价目表单据名称
                            "FPriceObject": "A",
                            "FPriceType": "2",
                            "FCurrencyID": {
                                "FNumber": "PRE001"          #币别
                            },
                            "FSupplierID": {
                                "FNumber": supplier_number          #供应商编码
                            },
                            "FIsIncludedTax": 1,
                            "FDefPriceListId": 0,
                            "FIsPriceExcludeTax": 1,
                            "FPriceListEntry": [
                                {
                                    "FMaterialId": {
                                        "FNumber": material_number          #物料编码
                                    },
                                    "FMaterialTypeId": {
                                        "FNumber": "CHLB01_SYS"
                                    },
                                    "FUnitID": {
                                        "FNumber": uom          #计量单位
                                    },
                                    "FPrice": price,          #不含税单价
                                    "FTaxPrice": taxprice,          #含税单价
                                    "FTaxRate": taxrate,          #税率
                                    "FPriceCoefficient": 1.0,
                                    "FDownPrice": taxprice,          #下限价格
                                    "FUpPrice": taxprice,          #上限价格
                                    "FEntryEffectiveDate": now(),          #生效日期
                                    "FEntryExpiryDate": "2100-01-01 00:00:00"
                                }
                            ]
                        }
                    }
        # 序列化json.dumps()
        post_data = { "FormID": "PUR_PriceCategory", "Data": json.dumps(post_options) }
        # 同步至K3cloud
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        
        r = json.loads(save_response.text)
        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
            frappe.msgprint(r["Result"]["Number"])    #调试
        else:
            frappe.msgprint(r["Result"]["ResponseStatus"]["Errors"][0]["Message"])

# 2023-03-26:写K3Cloud采购收料单  2023-05-12: 调用标准API方法
@frappe.whitelist()
def sync_to_k3cloud_receiveBill(model):
    pass
    acctid = frappe.get_all('SRM Demo AcctID', filters={'enable': 1, 'application': '收料通知单'},fields=['acctid'])
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.get_all('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if login_response:
        save_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc"
        submit_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Submit.common.kdsvc"
        audit_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Audit.common.kdsvc"
        post_options = {"Model":model}
        # 保存接口
        post_data = { "FormID": "PUR_ReceiveBill", "Data": json.dumps(post_options) }
        save_response = requests.post(url = save_url, data = post_data, cookies = login_response)
        r = json.loads(save_response.text)
        if r["Result"]["ResponseStatus"]["IsSuccess"] == True:
            return_bill_id = r["Result"]["Id"]
            submit_post_data = {"FormId":"PUR_ReceiveBill","Data":json.dumps({"Ids": return_bill_id }) }
            submit_response = requests.post(url = submit_url, data = submit_post_data, cookies = login_response)
            submit_r = json.loads(submit_response.text)
            # 提交接口
            if submit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                audit_post_data = {"FormId":"PUR_ReceiveBill","Data":json.dumps({"Ids": return_bill_id }) }
                submit_response = requests.post(url = audit_url, data = audit_post_data, cookies = login_response)
                audit_r = json.loads(submit_response.text)
                # 审核接口
                if audit_r["Result"]["ResponseStatus"]["IsSuccess"] == True:
                    return return_bill_id
                    # frappe.msgprint(r["Result"]["Number"])


# 根据采购订单分录内码获取相关的单价
@frappe.whitelist()
def get_po_price_by_poentryid(poentryids):
    ids = tuple(poentryids)
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '收料通知单'},fields=['acctid'], ignore_permissions=True)
    # 从server_config中获取server_url, acctid, user_name, user_password
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)
    
    if acctid:
        # 取测试账套ID
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    # 调用登录函数
    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {
            "data": json.dumps({
                "FormId": "PUR_PurchaseOrder",
                # PO之FID-0,PO单据编号-1,币别编码-2,分录内码-3,订单数量-4,单价-5,税率-6,含税单价-7
                "FieldKeys": "FID, FBillNo, FSettleCurrId.FNumber, FPOOrderEntry_FEntryID, FQty, FPrice, FEntryTaxRate, FTaxPrice",
                "FilterString": f" FPOOrderEntry_FEntryID in {ids} ",
                "OrderString": "FPOOrderEntry_FEntryID",
            })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        if res:
            return res


# 从ERP采购交期回复表-采购员 同步生成采购交期回复表-供应商 2023-03-29: 待作废
@frappe.whitelist()
def sync_plan_of_supplier_from_erp_old():
    acctid = frappe.db.get_list('SRM Demo AcctID', filters={'enable': 1, 'application': '交期回复表'},fields=['acctid'], ignore_permissions=True)
    
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    if acctid:
        # 取测试账套ID  63b7c666bf6b3c
        login_response = login_k3cloud(server_config[0].server_url, acctid[0].acctid, server_config[0].user_name, server_config[0].user_password)
    elif not acctid:
        # 取[服务器配置]中的acctid
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    
    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps({
                # 以下参数直接从云星空的WEB API中复制
                    "FormId": "JLKJ_CHGFJHCGY",
                    # F_JLKJ_SubEntity_FDetailID采购需求分配的实体主键, 纠正之前的F_JLKJ_Entity_FEntryID 
                    "FieldKeys": "FID, F_JLKJ_Entity_FEntryID, FBillNo, FPurchaserId.FName, F_JLKJ_CreatorId.FName, FISNEW, F_JLKJ_CreateDate, FPOENTRYID, FSUPPLIERID.FNumber, FSUPPLIERID.FName, FPOBILLNO, FMATERIALLID.FNumber, F_JLKJ_BaseProperty1, FMATERIALNAME, FMATERIALSPEC, FUnitID.FName, FPOQty, FINQty, FInspectQty, FText, Fsatisfy, FBillneedDate, FAssignQty, FPurchaserId.FName, FLeadTime, F_JLKJ_BaseProperty, F_JLKJ_SubEntity_FDetailID",
                    "FilterString": " FISNEW = 1 AND FAssignQty >0 AND FBillneedDate > '2023-01-01' ", # 只同步最新的,
                    "Limit": 0,
                    "OrderString": "FID, FSUPPLIERID.FNumber, FMATERIALLID.FNumber, FBillneedDate"      # 同一供应商按照物料编码\需求日期排序
                })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        # 判断res是否有效
        if res:
            for i in range(len(res)):
                # res中的FID和供应商组合存在SRM Supply Plan Of Supplier
                if frappe.db.exists("SRM Supply Plan Of Supplier", {"fid": res[i][0], "supplier_number": res[i][8]}, cache=True):
                    # FID存在判断分录行已存在
                    if frappe.db.exists("SRM Supply Plan Of Supplier OrderItem",{"subentity_fdetailid": res[i][26]}) or res[i][22] <= 0:
                        pass
                    # FID存在判断分录行不存在
                    else:
                        #新增分录行
                        doc_exists = frappe.get_list("SRM Supply Plan Of Supplier", {"fid": res[i][0], "supplier_number": res[i][8]}, ignore_permissions=True)
                        doc_exist = frappe.get_doc("SRM Supply Plan Of Supplier", doc_exists[0].name)
                        # doc_exist对象有append方法,增加子表行记录
                        # 增加行记录之前判断供应商编码是否在SRM中存在并且一致,不一致的就跳过
                        if frappe.db.exists("SRM Supplier", res[i][8]):
                            doc_exist.append('orderitem', {
                                'fentryid': res[i][1],                  # 行内码
                                'po_entryid': res[i][7],                # 采购订单行内码
                                # 'supplier_number': res[i][8],           # 供应商编码
                                # 'supplier_name': res[i][9].strip(),             # 供应商名称
                                'po_billno': res[i][10],                 # 采购订单号
                                'material_number': res[i][11],           # 物料编码
                                'old_material_number': res[i][12],       # 旧物料编码
                                'material_name': res[i][13].strip(),             # 物料名称
                                'material_model': res[i][14].strip(),            # 规格型号
                                'uom_name': res[i][15],
                                'order_qty': res[i][16],
                                'stock_qty': res[i][17],
                                'inspect_qty': res[i][18],
                                'description': res[i][19].strip(),              #备注
                                'fsatisfy': res[i][20],                 #满足需求
                                'need_date': res[i][21],                #需求日期
                                'assign_qty': res[i][22],               #分配数量
                                'purchase_person': res[i][23],          #采购员
                                'lt': res[i][24],
                                'mpq': res[i][25],
                                'subentity_fdetailid': res[i][26]
                            }).insert(ignore_permissions=True)
                        elif frappe.db.exists("SRM Supplier", {"supplier_number": res[i][8]}):
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp', 'error': f'ERP的供应商编码{res[i][8]}与Name不一致'})
                            error.insert(ignore_permissions=True)
                        else:
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp', 'error': f'ERP的供应商编码{res[i][8]}不存在'})
                            error.insert(ignore_permissions=True)
                # res中的FID不存在SRM Supply Plan Of Supplier
                else:
                    # 舍去分配数量为0的记录行
                    if res[i][22] <= 0:
                        pass
                    else:
                        # 增加行记录之前判断供应商编码是否在SRM中存在并且一致,不一致的就跳过
                        if frappe.db.exists("SRM Supplier", res[i][8]):
                            doc = frappe.get_doc({
                                'doctype': 'SRM Supply Plan Of Supplier', 
                                'fid': f'{res[i][0]}',
                                'fbillno': res[i][2],
                                'buyer': res[i][3],
                                'creatorer': res[i][4],
                                'is_last': res[i][5],
                                'createdate': res[i][6],
                                'supplier_number': res[i][8],           # 供应商编码
                                'supplier_name': res[i][9].strip(),             # 供应商名称
                                'orderitem': [{
                                    'fentryid': res[i][1],                  # 行内码
                                    'po_entryid': res[i][7],                # 采购订单行内码
                                    # 'supplier_number': res[i][8],           # 供应商编码
                                    # 'supplier_name': res[i][9].strip(),             # 供应商名称
                                    'po_billno': res[i][10],                 # 采购订单号
                                    'material_number': res[i][11],           # 物料编码
                                    'old_material_number': res[i][12],       # 旧物料编码
                                    'material_name': res[i][13].strip(),             # 物料名称
                                    'material_model': res[i][14].strip(),            # 规格型号
                                    'uom_name': res[i][15],
                                    'order_qty': res[i][16],
                                    'stock_qty': res[i][17],
                                    'inspect_qty': res[i][18],
                                    'description': res[i][19].strip(),              #备注
                                    'fsatisfy': res[i][20],                 #满足需求
                                    'need_date': res[i][21],                #需求日期
                                    'assign_qty': res[i][22],               #分配数量
                                    'purchase_person': res[i][23],          #采购员
                                    'lt': res[i][24],
                                    'mpq': res[i][25],
                                    'subentity_fdetailid': res[i][26]
                                }]
                            })
                            # doc.append("SRM Supply Plan OrderItem", {})新增时不支持该append,有错误提示:AttributeError: 'NoneType' object has no attribute 'options'
                            doc.insert(ignore_permissions=True)
                        elif frappe.db.exists("SRM Supplier", {"supplier_number": res[i][8]}):
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp', 'error': f'ERP的供应商编码{res[i][8]}与Name不一致'})
                            error.insert(ignore_permissions=True)
                        else:
                            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp', 'error': f'ERP的供应商编码{res[i][8]}不存在'})
                            error.insert(ignore_permissions=True)
                frappe.db.commit()
        else:
            error = frappe.get_doc({'doctype': 'Error Log', 'method': 'sync_plan_of_supplier_from_erp', 'error': 'ERP没有符合最新的数据返回'})
            error.insert(ignore_permissions=True)


# 2023-04-24: 从ERP同步货源清单,初始化一次
@frappe.whitelist()
def sync_sourcebill_from_erp_init(startrow):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps({
                "FormId": "PUR_CATALOG",
                "FieldKeys": "FPUR_CATALOGENTRY_FENTRYID, FBillNo, FDocumentStatus, FIsUse, FForbidStatus, FMaterialId.FNumber, FEtySupplierId.FNumber, FEtySupplierId.FName ",
                "FilterString": " FDocumentStatus = 'C' AND FForbidStatus = 'A' ",
                "OrderString": "FBillNo",
                "StartRow": startrow,
                "Limit": 2000
            })
        }
        # 返回是list
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        # 将json格式数据转换为字典
        res = json.loads(query_response.text)
        if res:     
            return res

# 2023-04-24: 货源清单初始化导入
@frappe.whitelist()
def init_sync_sourcebill_from_erp():
    # 初始化起始行为0
    startrow = 0
    # 初始化接收列表为空
    ls = []
    # 循环:百万条内
    while startrow < 100000:
        # 获取limit:2000条返回行
        r = sync_sourcebill_from_erp_init(startrow)
        # 判断有返回由往接收列表中抛并且把起始行变量+2000(为什么是+2000,跟limit匹配)
        if r and len(r) > 0:
            ls.append(r)
            startrow += 2000
        # 如果没有返回则跳出循环
        else:
            break
    # 有接收结果的处理规则: 在接收结果中2层循环
    # return ls
    if len(ls) > 0:
        for i in ls:
            for m in i:
                doc = frappe.get_doc({'doctype':'SRM Source Bill', 'erp_billno': m[1], 'fdocumentstatus': m[2], 'fisuse': m[3], 'fforbidstatus': m[4], 'subs': [{'material_number': m[5], 'supplier': m[6], 'fentryid': m[0] }] })
                doc.insert(ignore_permissions=True)
        frappe.db.commit()


# 2023-04-25: 定期从ERP查询14天到期的价目表行记录, 并且匹配合格供应商清单后发送邮件
@frappe.whitelist()
def week_get_pricecategory_from_erp():
    date_to = add_to_date(today(), days = 28)
    date_from = today()
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'])   
    # 2023-05-05: 增加对黑名单的识别
    blacklist = []
    blacklist_a = frappe.db.get_list('SRM Blacklist Items', fields=['supplier_number'])
    if blacklist_a:
        for i in blacklist_a:
            blacklist.append(i.supplier_number)

    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps ({
                "FormId": "PUR_PriceCategory",
                "FieldKeys": "FSupplierID.FNumber, FSupplierID.FName, FMaterialId.FNumber, FMaterialId.FName, FEntryExpiryDate, FDisableStatus   ",
                "FilterString": f" FDocumentStatus = 'C' AND FForbidStatus = 'A' AND FDisableStatus ='B' AND (FEntryExpiryDate < '{date_to}') AND (FEntryExpiryDate > '{date_from}')  ",
                "OrderString": "FSupplierID.FNumber",
            })
        }
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        res = json.loads(query_response.text)
        s = []
        if res:
            for i in res:
                # 2023-05-05: 增加对黑名单的识别
                if blacklist and blacklist.index(i[0]) >= 0:
                    pass
                else:
                    if len(s) == 0:
                        s.append({'supplier_number': i[0], 'supplier_name': i[1], 'items': [ i[2] + '/' + i[3] + '/' + i[4][:10] ] })
                    else:
                        index = len(s) - 1
                        if i[0] == s[index]["supplier_number"]:
                            s[index]["items"].append(i[2] + '/' + i[3] + '/' + i[4][:10] )
                        else:
                            s.append({'supplier_number': i[0], 'supplier_name': i[1], 'items': [ i[2] + '/' + i[3] + '/' + i[4][:10] ] })
            list1 = []
            for i in s:
                list1.append({'supplier_number': i['supplier_number'], 'supplier_name': i['supplier_name'], 'items': ','.join(i['items'])})
            if len(list1) > 0:
                for i in list1:
                    if frappe.db.exists("SRM Supplier", {"name": i["supplier_number"], "qualified": 1}):
                        if frappe.db.exists("SRM Supplier", {"name": i["supplier_number"]}):
                            supplier = frappe.get_doc("SRM Supplier", i["supplier_number"])
                            if supplier.contactbackgroud and supplier.contactbackgroud[0].email_address:
                                frappe.sendmail(
                                    recipients=supplier.contactbackgroud[0].email_address,
                                    sender="Administrator",
                                    reference_doctype="价目表预警",
                                    reference_name=i["supplier_number"],
                                    subject=f"{i['supplier_name']} 贵司有快到期的价目表记录请尽快提供新的报价",
                                    message=f"<p style='color:red;'> 本邮件属于系统发送，请勿直接回复 </p><br><p> 明细: {i['items']}</p>",
                                )
                            else:
                                error = frappe.get_doc({'doctype': 'Error Log', 'method': 'week_get_pricecategory_from_erp', 'error': f'联系人2没有邮箱或者邮箱不合法'})
                                error.insert(ignore_permissions=True)


# 2023-04-27:供应商更名处理,用查看接口比对ERP的名称,如果不一致则改之
@frappe.whitelist()
def day_sync_supplier_from_erp():
    s = frappe.db.get_list('SRM Supplier')
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    view_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.View.common.kdsvc"
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
    if len(s) > 0 and login_response:
        for i in s:
            doc = frappe.get_doc('SRM Supplier', i.name)
            view_options = {"FormId": "BD_Supplier", "data": json.dumps({"Number": i.name})}
            view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
            view_res = json.loads(view_response.text)
            if view_res:
                if view_res["Result"]["ResponseStatus"]["IsSuccess"] == True:
                    if doc.supplier_name == view_res["Result"]["Result"]["Name"][0]["Value"].strip():
                        pass
                    else:
                        # 2023-04-27: 更新供应商名称
                        frappe.db.set_value(doc.doctype, doc.name, {'supplier_name': view_res["Result"]["Result"]["Name"][0]["Value"].strip()})
        frappe.db.commit()


@frappe.whitelist()
def my_requests(query_url, post_options, login_response):
    # pass
    query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
    # 将json格式数据转换为字典
    res = json.loads(query_response.text)
    return res


@frappe.whitelist()
def auto_delete_email_queue():
    # 180天前的Email Queue永久删除delete_permanently=True
    for row in frappe.get_all("Email Queue", filters={"creation": ["<", add_to_date(now(), days=-180)]}, order_by="creation", page_length=100):
        frappe.delete_doc("Email Queue", row.name, ignore_permissions=True, delete_permanently=True)


# 从ERP单据查询动态FormId
@frappe.whitelist()
def dynamic_billquery_k3cloud(formid, fieldkeys, filterstring):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)

    if login_response:
        query_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
        post_options = {"data": json.dumps({
                    "FormId": formid,
                    "FieldKeys": fieldkeys,
                    "FilterString": filterstring
                })
        }
        query_response = requests.post(url=query_url, data=post_options, cookies=login_response)
        res = json.loads(query_response.text)
        
        return res



# 2024-04-02:当同步了有新的采购订单变更记录,把采购订单号传参进行采购订单同步
@frappe.whitelist()
def new_sync_po_from_erp_one_by_one(po_billno):
    server_config = frappe.db.get_list('SRM Server Config', filters = {'disable': 0}, fields = ['server_url', 'user_name', 'acctid', 'user_password'], ignore_permissions=True)   
    view_url = server_config[0].server_url + "Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.View.common.kdsvc"
    # 判断po_billno是否存在SRM
    if frappe.db.exists("SRM Purchase Order", po_billno):
        doc = frappe.get_doc('SRM Purchase Order', po_billno)
        login_response = login_k3cloud(server_config[0].server_url, server_config[0].acctid, server_config[0].user_name, server_config[0].user_password)
        if login_response:
            view_options = {"FormId": "PUR_PurchaseOrder", "data": json.dumps({"Number": po_billno})}
            view_response = requests.post(url=view_url, data=view_options, cookies=login_response)
            view_res = json.loads(view_response.text)
            if view_res:
                if view_res["Result"]["ResponseStatus"]["IsSuccess"] == True:
                    for item in view_res["Result"]["Result"]["POOrderEntry"]:
                        row = frappe.db.get_all('SRM Purchase Order Sub', filters = { 'po_entryid': item["Id"] } )
                        if row:
                            # 行记录更新
                            frappe.db.set_value('SRM Purchase Order Sub', row[0].name, 
                                                    {   
                                                        'closed': item["MRPCloseStatus"], 
                                                        'qty': item["Qty"], 
                                                        'request_date': item["DeliveryDate"], 
                                                        'price': item["Price"], 
                                                        'amount': item["Amount"], 
                                                        'tax_price': item["TaxPrice"], 
                                                        'tax_rate': item["TaxRate"], 
                                                        'all_amount': item["AllAmount"], 
                                                        'tax_amount': item["TaxAmount"], 
                                                        'freezestatus': item["MRPFreezeStatus"], 
                                                        'terminatestatus': item["MRPTerminateStatus"], 
                                                        'freceiveqty': item["ReceiveQty"], 
                                                        'fstockinqty': item["StockInQty"], 
                                                        'fremainreceiveqty': item["RemainReceiveQty"], 
                                                        'fremainstockinqty': item["RemainStockInQty"],
                                                        'price_list': item["F_XCD_CGJM"],   #是否价目表取数
                                                        'to_be_notified_for_shipment': item["F_XCD_CheckBox"],   #是否待通知发货
                                                    }, 
                                                    update_modified=False
                                                )
                    # 2024-04-02: 如果当前行记录是新增行, 则append
                        else:
                            new_row = {
                                        'material_number': item["MaterialId"]["Number"], #物料代码
                                        'uom': item["UnitId"]["Name"][0]["Value"],  #计量单位
                                        'qty': item["Qty"], #订单数量
                                        'request_date': item["DeliveryDate"],   #需求日期
                                        'price': item["Price"], #不含税单价
                                        'amount': item["Amount"],
                                        'tax_rate': item["TaxRate"],
                                        'tax_price': item["TaxPrice"],
                                        'tax_amount': item["TaxAmount"],
                                        'all_amount': item["AllAmount"],
                                        'fnote': item["Note"],  #备注
                                        'po_entryid': item["Id"],   #订单行ID
                                        'po_entry_seq': item["Seq"],    #订单行分录号
                                        'closed': item["MRPCloseStatus"], #业务关闭
                                        'freezestatus': item["MRPFreezeStatus"],    #业务冻结
                                        'terminatestatus': item["MRPTerminateStatus"],   #业务终止
                                        'price_list': item["F_XCD_CGJM"],   #是否价目表取数
                                        'to_be_notified_for_shipment': item["F_XCD_CheckBox"],   #是否待通知发货
                            }
                            doc.append("purchase_order_sub", new_row)
                    # 2023-04-19: 更新单据头字段, 来源于view_res["Result"]["Result"]["CloseStatus"]      
                    frappe.db.set_value(doc.doctype, doc.name, {'doc_closed': view_res["Result"]["Result"]["CloseStatus"], 'fclosestatus': '已关闭' if view_res["Result"]["Result"]["CloseStatus"] == 'B' else '未关闭'}, update_modified=False)
                    doc.reload()
                    # doc.save()